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, Aug 12 '12 at 07:40

score 2 · Accepted Answer · edited May 23 '17 at 12:04

2

Stackoverflow already has an existing answer:

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

edited May 23 '17 at 12:04

Community

answered Aug 12 '12 at 08:04

Michael Buen

XN16 · Answer 2 · 2012-08-12T09:06:11.593

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), '')

edited Aug 12 '12 at 09:06

answered Aug 12 '12 at 07:49

XN16

@nambla What it the problem with it? Works for me. – XN16 Aug 12 '12 at 08:18
@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! – XN16 Aug 12 '12 at 09:05

2 Answers2