Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Unanswered: how to eliminate blanks from a string

    I have a varchar field which will contain a long numeric identifier, similar to a telephone number. I don't mind if it is stored with embedded separators, like
    123-456-7890
    When extracting the data, I want to eliminate the separators, so my stored proc should return the same data as:
    1234567890

    The obvious solution would be

    str_replace( my_column, '-', '' )

    But wait: the documentation says: "The empty string ( ) is treated as a single space.". In consequence, the replace command produces a string which has one blank instead of each of the dashes. result:

    123 456 7890

    In other situations, the str_replace() function seems to have no problem producing strings which are longer or shorter than the original. Only the empty string causes problems.

    Did anyone figure out a way to replace a sequence of characters by an empty string ?

    I don't know in advance how many blanks my string will contain. I could write a loop, using charindex, then taking the portion after and before - yukk

    Andrew

  2. #2
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    sorry guys, my project was urgent, so I had to put in that yukky solution.

    My question still stands - does any one know a more elegant solution ?

    Here is that ugly code -- runs as a self-standing test.


    -- how to eliminate blanks from strings
    -- beware, str_replace() treats the empty string as a blank

    select mst = convert( varchar(50), ' alpha beta gamma delta ' ),
    blank_pos = 0 -- 1 3 5 7 9 1 3 5 7 9 1
    into #tmp
    union
    select mst = convert( varchar(50), 'more blanks here' ),
    blank_pos = 0

    -- actual code starts here
    update #tmp set mst = ltrim( rtrim( mst ))

    declare @fnd int
    select @fnd = 1 -- always do at least one iteration of the loop
    while @fnd > 0
    begin

    update #tmp set blank_pos = charindex( ' ', mst )

    -- re-compose the string, but only if there are two
    -- non-blank portions before and after a blank

    update #tmp set mst = ltrim( rtrim( substring( mst, 1, blank_pos - 1) ) ) +
    ltrim( rtrim( substring( mst, blank_pos, 200 ) ) )
    where blank_pos > 1 -- leading blank ignored by other means
    and blank_pos < char_length( mst ) -- same for trailing

    select @fnd = @@rowcount -- stop the loop if no more changes to be done

    end

    select mst from #tmp -- check the results

    drop table #tmp

  3. #3
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    If you have a reaonably recent version of ASE (12.5.2 or later, IIRC) you can do:

    select str_replace("123-456-7890", '-', null);

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1234567890

    Michael

  4. #4
    Join Date
    Aug 2003
    Posts
    43
    Quote Originally Posted by andrewsc
    sorry guys, my project was urgent, so I had to put in that yukky solution.

    My question still stands - does any one know a more elegant solution ?

    Here is that ugly code -- runs as a self-standing test.


    -- how to eliminate blanks from strings
    -- beware, str_replace() treats the empty string as a blank

    select mst = convert( varchar(50), ' alpha beta gamma delta ' ),
    blank_pos = 0 -- 1 3 5 7 9 1 3 5 7 9 1
    into #tmp
    union
    select mst = convert( varchar(50), 'more blanks here' ),
    blank_pos = 0

    -- actual code starts here
    update #tmp set mst = ltrim( rtrim( mst ))

    declare @fnd int
    select @fnd = 1 -- always do at least one iteration of the loop
    while @fnd > 0
    begin

    update #tmp set blank_pos = charindex( ' ', mst )

    -- re-compose the string, but only if there are two
    -- non-blank portions before and after a blank

    update #tmp set mst = ltrim( rtrim( substring( mst, 1, blank_pos - 1) ) ) +
    ltrim( rtrim( substring( mst, blank_pos, 200 ) ) )
    where blank_pos > 1 -- leading blank ignored by other means
    and blank_pos < char_length( mst ) -- same for trailing

    select @fnd = @@rowcount -- stop the loop if no more changes to be done

    end

    select mst from #tmp -- check the results

    drop table #tmp
    A bit simpler one shown below and I dont think any simpler function exists for Sybase versions earlier than 12.5.2

    declare @myint int
    declare @var char(100)
    select @var="abcd efe hdfh es sgs" /* for testing */
    while (@myint <> 0)
    begin
    select @var = substring(@var,1,charindex(" ",@var)-1)+substring(@var,charindex(" ",@var)+1, char_length(@var))
    select @myint = charindex(" ",rtrim(@var))
    end
    select @var

  5. #5
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    thanks for your answer,

    > 12.5.2 or later, IIRC) you can do:

    > select str_replace("123-456-7890", '-', null);

    Is this a general change in handling the "empty string" ?
    Or, just an specific improvement for the str_replace function ?

  6. #6
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    It is specific to str_replace(). An empty string ("") will still be converted to a single space.

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •