SQL Server Performance Forum – Threads Archive

Nulls

Hi, Is there an easy way to remove null characters char(0) from a string or a column in a table? I did some search through help topics, but couldnt find a way. thanks,
Sunil

try with isnull isnull(field,”) – string
isnull(field,0) – integer
SURYA

Doesnt seem to work! I have a string like abcdefg with rest of characters as char(0)s like ‘abcdefg ‘ (not spaces). I want to trim the nulls and get the string back. Version is SQL 2k. thanks.
Sunil

Try: update table
set stringColumn = REPLACE(stringColumn,char(0),”)

Hi, I tried that. When i use replace with char(0), i get nothing back. The whole string is nullified! thanks,

Not sure if I understand you, but I would probably CAST to VARBINARY and then remove the hex characters. Or I would do it in some client scripting language. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

If all char(0) are at the end of the string you can also try: update table
set stringColumn = substring(stringColumn,
1,
case
when charIndex(char(0), stringColumn) between 0 and 1 then len(stringColumn)
else charIndex(char(0), stringColumn) – 1
end
)

Not only at the end of the string, but also at the beginning … UPDATE table
SET column = SUBSTRING(REPLACE(‘_’ + column + ‘_’, CHAR(0), ”), 2, (LEN(column) – 1)) By the way, is the column of variable length (like VARCHAR) or of fixed length (like CHAR)?

LEN function didnt work for me. It gives the length including the nulls!. The field is type varchar but seems to be padded with nulls till the end of the field. I wrote a function to read the string character by character and remove all ascii(0)s from it. Thanks guys. Sunil

You are right about len, in my example it works only when charIndex returns 0. When it is 1 it should be separate case branch returning empty string.

COuld it be that the table was created with …
SET ANSI_PADDING ON
… before it in the script?

Hi Marovic, On matter of interest, if charindex returns the first occurance of a string, why this returns 1? select charindex(char(0),’SQL Server’)
———–
1 (1 row(s) affected) select charindex(‘L’,’SQL Server’)
———–
3 (1 row(s) affected) Sunil.

In SQL Server 2000, Running select charindex(char(0),’SQL Server’) gives 0
Which version of SQL Server are you having? Madhivanan Failing to plan is Planning to fail

You are right, I’ve just tested and figured out that neither patindex or charindex can handle char(0) properly.

I guess the problem is that string implementation in most cases have a null character as the end of string designator.

The reason is probably very simple. In SQL Server CHAR(0) marks the end of a string. QA just stops displaying characters after determining a CHAR(0). That’s why
select char(0)+’SQL Server’ doesn’t display anything, while
SELECT ‘SQL Server’+CHAR(0) ———–
SQL Server (1 row(s) affected) LEN returns the number of character in a string, not the displaying length. Actually I think most languages use CHAR(0) for marking the end of a string. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Try next code: declare @string varchar(50)
declare @string1 char(50) select @string = ‘123’+char(0) +’5′, @string1 = ‘123’+char(0)+’5′ select @string, charIndex(char(0), @string), patIndex(char(0), @string), @string1, charIndex(char(0), @string1), patIndex(char(0), @string1)
go

Declare @t table(v varchar(100))
insert into @t select char(0)+’SQL Server’ union all select ‘test’+char(10) union all select ‘test’
update @t set v=replace(v,char(0),”)
select v from @t where v like ‘%’+char(0)+’%’ Madhivanan Failing to plan is Planning to fail

I can reproduce the result as 1 only by using an Nvarchar type variable: declare @test nvarchar(100)
set @test = ‘SQL Server’
select charindex(char(0), @test) This is because each character in the string is covered by a single byte, which is the second of the two bytes used for each character in a unicode string. The first byte is 0, so with charindex you get the first position.

wish if RTRIM can trim more than just spaces….
Like RTRIM (‘SQL Server’,’Server’) giving back only ‘SQL ‘, thus RTRIM (‘SQL Server’,char(0)) trimming unwanted nulls from end! Sunil

]]>