REPLACE to get rid of unwanted characters

• AndreQ

  SSCommitted

  Points: 1540

  More actions

  July 23, 2003 at 3:44 pm

  #159551

  I may have posted this question already. My brain has been a bit fried so please answer again, if you've already done so.

  I wanted to find a technique to find the rows where a column has more than one comma. We have data returned to us, delimited by commas and I want to get a quick heads-up to see if there is more than one comma in any given row.

  My first technique was as follows:

  select IDColumn

  , DataValue

  from TableCheck

  where len(DataValue) > len( replace(DataValue, ',', null ) plus 1

  <Note, the plus sign didn't show on Preview, so I spelled it out>

  With the above, when removing the commas, it shrinks the length of the varchar column. If the shrinkage is more than 1, then there's more than 1 comma. I was pretty proud of myself about this technique, but the REPLACE acted strangely. Instead of shrinking the length, it had kept it the same. The comma was replaced by a space.

  I got around this by replacing the comma with || and checking for proportionate INCREASE in length, but am I missing something here? I'm using SQL 7 on NT 4.0.

  Thanks

  Andre

• GPSmith

  SSC Enthusiast

  Points: 104

  More actions

  July 24, 2003 at 2:23 am

  #466722

  How about simply:

  select IDColumn

  , DataValue

  from TableCheck

  where DataValue like '%,%,%'

• LeopoldNjampa

  SSC-Addicted

  Points: 473

  More actions

  July 24, 2003 at 4:46 am

  #466756

  You could use this:

  create table #temp

  (

  rowid int,

  dataval varchar(30)

  )

  insert #temp(rowid, dataval)

  select 1, 'one,two,three,,' union all

  select 2, 'one,two three four' union all

  select 3, 'one,twothree' union all

  select 4, 'one,two,three,four' union all

  select 5, 'one,two three'

  select rowid, dataval

  from #temp

  where len(ltrim(rtrim(dataval))) - len(replace(ltrim(rtrim(dataval)), ',','')) >= 2