Detecting Specific Characters in a Varchar Field

• Grinja

  Hall of Fame

  Points: 3590

  More actions

  August 11, 2008 at 2:01 am

  #196740

  Hi,

  I would like to return the results from Varchar field where there are spaces in the field.

  Is there a way to limit results to where there are empty spaces or characters such as '_ , *, .)

  Any links to string manipulation would also be appreciated.

  Thanks

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  August 11, 2008 at 2:12 am

  #855933

  You can get the location of the characters you are looking for by using patindex

  e.g.

  declare @a varchar(10)

  set @a = 'abcd a_b'

  select patindex('%[ _]%', @a)

  If you need to remove things, you can use replace, substring, ...

  You can read on string manipulation functions that are available in T-SQL on http://msdn.microsoft.com/en-us/library/ms181984.aspx

  However, if you need something more complicated, you may way to look at CLR functions.

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• Grinja

  Hall of Fame

  Points: 3590

  More actions

  August 11, 2008 at 6:08 am

  #856011

  Thank you very much for this, this is exactly what i'm trying to accomplish.

  :hehe:

• $QLdb@

  Old Hand

  Points: 388

  More actions

  March 18, 2011 at 6:33 am

  #1300446

  this is really great

• terrykzncs

  Ten Centuries

  Points: 1324

  More actions

  March 30, 2011 at 6:52 am

  #1305402

  declare @a varchar(10)

  set @a = 'abcd a_b'

  select patindex('%[ _]%', @a)

  An alternative would be to use

  declare @a varchar(10)

  set @a = 'abcd a_b'

  select charindex('_', @a)

  I don't see a difference between the 2 functions. I posted this code as this is what I would have gone for first. Maybe someone can provide more info?

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  March 30, 2011 at 7:08 am

  #1305416

  The patindex is checking for two chars _ and space

  charindex('_', @a) will return position of first _ char

  patindex('%[ _]%', @a) will return position of EITHER _ or space

  charindex('_', @a) and patindex('%[_]%', @a) would give the same result (ie no space)

  if checking for presence of _ and space then

  CHARINDEX('_', @a) > 0 AND CHARINDEX(' ', @a) > 0

  would be the same as

  PATINDEX('%[ _]%', @a) > 0

  and it would get worse if you wanted to check for the letters a to f as you would have to OR 6 CHARINDEX

  or use PATINDEX('%[a-f]%', @a)

  Far away is close at hand in the images of elsewhere.
  Anon.

• terrykzncs

  Ten Centuries

  Points: 1324

  More actions

  March 30, 2011 at 7:18 am

  #1305424

  Ahem.....

  Just did some investigating....:blink:

  declare @a varchar(10)

  set @a = 'abcd a_b'

  select charindex('cd', @a) -- matches a pattern - 'cd' is found in @a

  select charindex('ce', @a) -- matches a pattern - 'ce' not found in @a

  select patindex('%[zce]%', @a) -- treats each of the letters as seperate and tries to find any

  --1 of them in @a

  select patindex('%[ce]%', @a) -- same outcome as above

  Still let me know if there is any other difference between the 2. It may help someone else reading this post.

• terrykzncs

  Ten Centuries

  Points: 1324

  More actions

  March 30, 2011 at 7:26 am

  #1305430

  Thanks David.

  Didnt see your reply there as I was typing out mine. The guys here on SQL ServerCentral reply really quick:smooooth:

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  March 30, 2011 at 8:33 am

  #1305468

  You're welcome

  Far away is close at hand in the images of elsewhere.
  Anon.