Best FUNCTION to search for a character inside a string sql?

Question

What is the best FUNCTION to search for a character inside a string sql?

I've been trying to search if there exists a character inside a fixed string.

For example I have:

1OF2. 040713 08:07 AM

And I want to know if there is a '.' inside the string.

Solution 1

Use like:

select col like '%.%'

It is standard SQL and SQL Server has some good optimizations built into the query engine to handle it.

EDIT (in response to a comment):

Getting the first part of the string is a bit different. In that case:

select (case when col like '%.%' then left(col, charindex('.', col) - 1)
             else col
        end)

Solution 2

CHARINDEX is also available. Use CHARINDEX and compare against 0 (not found). For example:

SELECT *
WHEN CHARINDEX('.',field) > 0

Comments

[Matt Ball]

Have you tried LIKE?

[fvrghl]

Please don't use subjective wording in your questions if you want good responses. It's best to avoid 'what is the best...'.

[zerkms]

"has some good optimizations" --- that's interesting. What kind of optimizations?

[ajdeguzman]

I want to remove the left part of the '.' For example 1OF2. 040713 08:07 AM and I want 040713 08:07 AM

[Gordon Linoff]

For instance, if you have xxx%, then it knows to do an index lookup on the prefix.

[zerkms]

@Gordon Linoff: every DB does that I suppose

[zerkms]

@Aj De Guzman: so why didn't you ask it in the original question but now?!?!?!

[ajdeguzman]

@GordonLinoff Can you cite an example of what you're trying to say? I'm a newbie in MS SQL.

[ajdeguzman]

@zerkms I've been trying this: DECLARE @mySample varchar(255) SET @mySample = '1OF2. 040713 08:07 AM' (SELECT convert(datetime, (STUFF(STUFF(((SUBSTRING(@mySample,CHARINDEX('.',@mySample,1‌​)+2, LEN (@mySample)))),3,0,'-'),6,0,'-'))))

[ajdeguzman]

@zerkms all i want to do is to convert the string '1OF2. 040713 08:07 AM' into datetime. but before that, i want to remove the 1OF2 thing for better conversion

[ajdeguzman]

@zerkms No. I did try what i pasted a while ago. I did search about STUFF SQL CHARINDEX etc. I'm purely a newbie with these things.

[t-clausen.dk]

I tested charindex vs like. Like worked faster on 6 mil rows. Can you document this claim ?

[Hogan]

@t-clausen.dk - Good to know -- thinking on it, I last tested it quite a while back. It seems my observations from then are no longer valid, answer edited.

[Hogan]

@t-clausen.dk - I'd be interested to know if it was faster when you are doing both like in Gorden's answer case when col like '%.%' the left(col, charindex('.', col) - 1) else col end my guess is this is faster case when charindex('.', col) > 0 then left(col, charindex('.', col) - 1) else col end