Detecting Specific Characters in a Varchar Field

Question

Post reply

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

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 · Answer 2

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

:hehe:

$QLdb@ Old Hand Points: 388 More actions · Answer 3

$QLdb@

Old Hand

Points: 388

March 18, 2011 at 6:33 am

#1300446

this is really great

terrykzncs Ten Centuries Points: 1324 More actions · Answer 4

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: 65143 More actions · Answer 5

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 · Answer 6

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 · Answer 7

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: 65143 More actions · Answer 8

You're welcome 🙂

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