SQL delete NULL containing strings

Question

I have a table that contains a list of titles.

And due to a problem something went wrong and the values now are like

title1[null]
title2[null][null]
title[null]3[null]

And the likes, I need to replace all [NULL] to NOTHING. so everything will be okay again. THank you.

Please advice.

PS.

[null] is not an actual string but the value NULL itself like the hex 00.

Your schema and values aren't understandable, plz elaborate more, or use SQL fiddle or some other tool to convey your problem. — yogi
– yogi, Commented Aug 12, 2012 at 7:40

Community · Accepted Answer · 2017-05-23 12:04:44Z

2

Stackoverflow already has an existing answer:

REPLACE(myString, char(0), '')

CommunityBot

11 silver badge

answered Aug 12, 2012 at 8:04

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

XN16 · Accepted Answer · 2012-08-12 09:06:11Z

1

This is for MS SQL

 UPDATE table_name SET column_name = REPLACE(column_name, '[null]', '')

NEW ANSWER AFTER UPDATE ABOUT '[NULL]' BEING THE NULL CHARACTER, NOT TEXT:

 UPDATE table_name SET column_name = REPLACE(column_name, char(0), '')

answered Aug 12, 2012 at 7:49

XN16

5,88916 gold badges52 silver badges76 bronze badges

@nambla What it the problem with it? Works for me.

@nambla Ah I get the issue now... I thought you meant the text '[null]' was in the string, not the actual null character! Answer updated!