Can't Replace Blank Value on a NVARCHAR

Question

Can't Replace Blank Value on a NVARCHAR

AVB

SSChampion

Points: 11418
More actions
January 7, 2011 at 6:57 am

#74628

I have an nvarchar column that has a bunch of blank values. When I run a replace command on it the blank values are not replaced. Not sure what the catch is as I'm sure it has something to do with it being nvarchar.
select replace(Weight1,'','0') From tTable1
That command yields a bunch of blank values however if I select from the table with a Where = '' clause a record set is returned with many rows.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

from books on line

string_pattern
Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string ('').

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

UDPATE T SET Weight1 = '0' from dbo.tTable1 WHERE Weight1 = ''

Also it might be worth it to convert that column to an integer or decimal. Then keep the weigth type in a separate column (Kg, lbs...).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

steveb. (1/7/2011)
from books on line
string_pattern
Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string ('').

Or put another way. How do you find nothing in something? And where is it when you find it?

:w00t:

AVB SSChampion Points: 11418 More actions · Answer 4

steveb,

Thanks for the reply. Well that explains it. My BOL doesn't say anything about that in the Replace String Function command explanation. Go figure.

AVB SSChampion Points: 11418 More actions · Answer 5

Ninja's_RGR'us (1/7/2011)
UDPATE T SET Weight1 = '0' from dbo.tTable1 WHERE Weight1 = ''
Also it might be worth it to convert that column to an integer or decimal. Then keep the weigth type in a separate column (Kg, lbs...).

Yeah I didn't design this table. It was done by a front end guy. This is a great example of why choosing the right datatype is so important. At least the weight type is in a separate column but everything is nvarchar - lol. Time to tidy up this mess. .

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

AVB (1/7/2011)
Ninja's_RGR'us (1/7/2011)
UDPATE T SET Weight1 = '0' from dbo.tTable1 WHERE Weight1 = ''
Also it might be worth it to convert that column to an integer or decimal. Then keep the weigth type in a separate column (Kg, lbs...).
Yeah I didn't design this table. It was done by a front end guy. This is a great example of why choosing the right datatype is so important. At least the weight type is in a separate column but everything is nvarchar - lol. Time to tidy up this mess. .

Well assuming clean data, sql server will convert '' to 0 so there may not even be a need for the update.

steveb. SSC-Forever Points: 46733 More actions · Answer 7

AVB (1/7/2011)
steveb,
Thanks for the reply. Well that explains it. My BOL doesn't say anything about that in the Replace String Function command explanation. Go figure.

no worries, its in the 2008r2 BOL but maybe they missed it out of 2000 version

AVB SSChampion Points: 11418 More actions · Answer 8

yeah It's not clean b/c when I try to do some calculations on it it fails after I try converting it to a decimal or int. I have a function I wrote for removing non-numeric characters from data. I'll zip it through that! 🙂