Find a character in string

Question

Post reply

Find a character in string

SQL_Kills

Hall of Fame

Points: 3154
More actions
September 25, 2013 at 4:18 pm

#395555

Hi,
Is there a sql function that will look for a character within a string and return a 1 or 0 if this character is showing?
For example if the below sql select statement has a chratcer of '@' i want it to show a result of 1?
Select 'AGDJS@JDJD' --will show as 1
Select 'AGDJSJDJD' --will show as 0
I just want to know if there is a current sql function that does this or if anyone can think of a case statement to get round this.
I don't want a User Defined Function if possible.
Thanks

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Try CHARINDEX and you could CAST it to bit so you will only get 1's and 0's.

Select CAST( CHARINDEX('@', 'AGDJS@JDJD') AS bit)--will show as 1

Select CAST( CHARINDEX('@', 'AGDJSJDJD') AS bit) --will show as 0

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

alekya SSC Journeyman Points: 82 More actions · Answer 2

HI,

Below query will find if there are any special character

in string

DECLARE @test-2 VARCHAR(50)

SET @test-2 = 'MySt%ring'

Select Convert(BIT,PATINDEX('%[^a-zA-Z0-9]%',@Test))

Hope this will help you :-):-D

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Could do this with a case expression also so there is no need to use CAST.

select case when CHARINDEX('%', @test-2) > 0 then 1 else 0 end

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Sean Lange (9/27/2013)
Could do this with a case expression also so there is no need to use CAST.
select case when CHARINDEX('%', @test-2) > 0 then 1 else 0 end

But CAST will reduce 34% of the code length 😛

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

Neither CAST nor CASE is actually necessary here:

SIGN(CHARINDEX('@', <string_value>))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

ScottPletcher (9/27/2013)
Neither CAST nor CASE is actually necessary here:
SIGN(CHARINDEX('@', <string_value>))

+1 I've never used it before and had forgotten this function.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

ScottPletcher (9/27/2013)
Neither CAST nor CASE is actually necessary here:
SIGN(CHARINDEX('@', <string_value>))

Excellent!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/