SQL SERVER (T-SQL)- PATINDEX
In Sql Server PatIndex is really useful method works like SPLIT funcation in .net, It returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
Sometimes you received data in a concated form and you need to seperated on the fly.
I will not go in more detail, here is the example will compelety describe how important PatIndex is.
Create table #temp
(
ZipCode Varchar(255),
Region Varchar(255))
Insert into #Temp values(’00000-00399′,’9′)
Insert into #Temp values(’00400-00599′,’8′)
Insert into #Temp values(’00600-00999′,’1′)
Select * from #Temp
Select  Left(ZipCode,patindex(‘%-%’,ZipCode)-1) as ZipCodeFrom ,
   Right(ZipCode,patindex(‘%-%’,ZipCode)-1) as ZipCodeTo,
   Region
FromÂ
    #TempÂ
 
You know, the thing about SQL is, that there is virtually nothing that can replace it.
Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.
let’s be serious, your example will not display as you wrote if the elements on the left and right of the tokenizer have different lengths.
more appropriate would be:
Select
Left(ZipCode,patindex(‘%-%’,ZipCode)-1) as ZipCodeFrom ,
Right(ZipCode, LEN(ZipCode) – patindex(‘%-%’,ZipCode)) as ZipCodeTo