SQL Server Performance

Nulls

Discussion in 'General Developer Questions' started by sunilthomas98, Dec 16, 2005.

  1. sunilthomas98 New Member

    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
  2. vsnreddi New Member

    try with isnull

    isnull(field,'') - string
    isnull(field,0) - integer




    SURYA



  3. sunilthomas98 New Member

    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
  4. mmarovic Active Member

    Try:

    update table
    set stringColumn = REPLACE(stringColumn,char(0),'')


  5. sunilthomas98 New Member

    Hi,

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

    thanks,
  6. FrankKalis Moderator

  7. mmarovic Active Member

    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
    )
  8. Adriaan New Member

    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)?
  9. sunilthomas98 New Member

    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
  10. mmarovic Active Member

    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.
  11. Adriaan New Member

    COuld it be that the table was created with ...
    SET ANSI_PADDING ON
    ... before it in the script?
  12. sunilthomas98 New Member

    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.

  13. Madhivanan Moderator

    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
  14. mmarovic Active Member

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

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

    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)
  17. mmarovic Active Member

    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
  18. Madhivanan Moderator


    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
  19. Adriaan New Member

    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.
  20. sunilthomas98 New Member

    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

Share This Page