Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Detecting Specific Characters in a Varchar Field Expand / Collapse
Author
Message
Posted Monday, August 11, 2008 2:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:24 AM
Points: 133, Visits: 617
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
Post #550073
Posted Monday, August 11, 2008 2:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #550076
Posted Monday, August 11, 2008 6:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:24 AM
Points: 133, Visits: 617
Thank you very much for this, this is exactly what i'm trying to accomplish.
Post #550169
Posted Friday, March 18, 2011 6:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 4:49 AM
Points: 8, Visits: 225
this is really great
Post #1080307
Posted Wednesday, March 30, 2011 6:52 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, March 4, 2012 4:02 AM
Points: 660, Visits: 134
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?
Post #1086171
Posted Wednesday, March 30, 2011 7:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 7,056, Visits: 7,281
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.

Post #1086189
Posted Wednesday, March 30, 2011 7:18 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, March 4, 2012 4:02 AM
Points: 660, Visits: 134
Ahem.....

Just did some investigating....


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.
Post #1086197
Posted Wednesday, March 30, 2011 7:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, March 4, 2012 4:02 AM
Points: 660, Visits: 134
Thanks David.

Didnt see your reply there as I was typing out mine. The guys here on SQL ServerCentral reply really quick
Post #1086204
Posted Wednesday, March 30, 2011 8:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 7,056, Visits: 7,281
You're welcome


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

Anon.

Post #1086247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse