CODEWITHSUNDEEP
sqlsql-serverstringcharindex
not_ur_avg_cookie
not_ur_avg_cookie

Reputation: 333

How to use Charindex for one or the other character

I have a string with a bunch of numbers but it contains one letter somewhere in the center of the string. This letter can either be 'A' or 'B'. I am trying to find out the position of this letter with the Charindex() function. However it doesn't work when you have two search parameters:

select  charindex('[A,B]','190118A3700000')

I tried it out with a range and wildcards but it did not work. So what I want are these two separate queries combined in one:

select  charindex('A','190118A3700000')
select  charindex('B','190118A3700000')

Does anybody have an idea how to do this?

Thank you!!!

Upvotes: 3

Views: 5720

Answers (3)

Sodais Adam
Sodais Adam

Reputation: 1

Substring('SQL Query', 4 charindex('L', 'SQL Query'))

Upvotes: 0

Rajat
Rajat

Reputation: 5803

If charindex doesn't find that character, it returns 0 so all you need to do is

select col, charindex('A', col) + charindex('B', col) as position
from your_table;

Another alternative

select col, charindex('A', replace(col, 'B', 'A')) as position
from your_table;

DEMO

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Use patindex():

select patindex('%[A,B]%', '190118A3700000')

Or, if you want the first non-digit:

select patindex('%[^0-9]%', '190118A3700000')

Here is a db<>fiddle.

charindex() does not understand wildcards.

Upvotes: 6

Related Questions