August 11, 2008 at 2:01 am
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
August 11, 2008 at 2:12 am
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
August 11, 2008 at 6:08 am
Thank you very much for this, this is exactly what i'm trying to accomplish.
:hehe:
March 18, 2011 at 6:33 am
this is really great
March 30, 2011 at 7:08 am
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.
March 30, 2011 at 7:18 am
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.
March 30, 2011 at 7:26 am
Thanks David.
Didnt see your reply there as I was typing out mine. The guys here on SQL ServerCentral reply really quick:smooooth:
March 30, 2011 at 8:33 am
You're welcome 🙂
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply