Using CHARINDEX to find EITHER/OR pattern

Question

Using CHARINDEX to find EITHER/OR pattern

FreeHansje

SSChampion

Points: 11751
More actions
September 30, 2008 at 6:42 am

#72889

I'm looking for a way to split a strings in a table at a point, where 1 of 2 values exist: either a ' '(space) or a '-'.
Some examples:
'Arat-Kara' would be seperated in 'Ara' and 'Kara'
'Arat Kara' would also be seperated in 'Ara' and 'Kara'
'Arat-Kara Bara' would be seperated in 'Ara' and 'Kara' and 'Bara'
Using CHARINDEX I can do this for either of the 2 seperators, but I cannot fiond a way to use BOTH seperators to split a string. I can make a workaround first splitting everything with a '-' and then go through the table again, now searching for strings with ' ', but that would be inefficient. Maybe there is no way to do this in 1 statement; help, any1?
Greetz,
Hans Brouwer

Viewing 4 posts - 1 through 3 (of 3 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

You should be able to use PATINDEX for that. At least you can in 2005 (I don't recall there being any major change to patindex between 2000 and 2005).

select patindex('%[ \-]%','this is a test of the emergency broadcast system.')

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Tested the above patindex string under 2K and it works fine.

OP:In your third example, you split the string twice. You will need to run the previous solution multiple times(with slight modifications), or come up with another method of doing this to actually split the string column into more than 2 parts.

You should be able to handle this with a tally table: http://www.sqlservercentral.com/articles/TSQL/62867/

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

FreeHansje SSChampion Points: 11751 More actions · Answer 3

Tnx for answering all. I do use a construction with Tallytable to split the string, I could not figure out how to choose from 2 patterns to choose from. I used CHARINDEX, but it should not be a problem to change that to PATINDEX.

Tnx again.

Greetz,
Hans Brouwer