Finding a Delimiter in SQL 3

[ousoonerjoe]

I have a field that is storing anywhere from 0 (NULL) to 3 values (all chars). The delimiter is "/". NOTE: The field will have UP TO 3 values, but could be NULL.

Sample data: 'GL/PRIMARY' or 'GL/PRIMARY/OCCURANCE'

How do parse out the 3 values to 3 different columns?

ie: [CoverageName] = c.UserDef2 (1st part),
[CoverageLayer] = c.UserDef2 (2nd part),
[CoverageForm] = c.UserDef2 (3rd part),


Any and all suggestions are welcome and appreciated.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[bborissov]

UPDATE YourTable SET CoverageName  = LEFT(OtherField, CHARINDEX('/',OtherField) - 1),
                     CoverageLayer = LEFT(SUBSTRING(OtherField, CHARINDEX('/',OtherField) + 1),
     CHARINDEX('/',SUBSTRING(OtherField, CHARINDEX('/',OtherField) + 1,8000))-1),
                     CoverageForm = RIGHT(OtherField, CHARINDEX('/',REVERSE(OtherField))-1)

NOT TESTED ytou should try it first with:

SELECT LEFT(OtherField, CHARINDEX('/',OtherField) - 1),
       LEFT(SUBSTRING(OtherField, CHARINDEX('/',OtherField) + 1),
     CHARINDEX('/',SUBSTRING(OtherField, CHARINDEX('/',OtherField) + 1,8000))-1),
       RIGHT(OtherField, CHARINDEX('/',REVERSE(OtherField))-1)
FROM YourTable

Where OtherField is the field where you keep current values.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[LNBruno]

Here's another take on a similar question:

http://www.tek-tips.com/viewthread.cfm?qid=1392377

< M!ke >
[small]Don't believe everything you think.[/small]

 

[ptheriault]

Here is a script I have that was used to split ',' delimintated fields. I changed it to '/' for your example. But something like this should work..

Declare @Original Table(Risk_Id Int, Risk_Num VarChar(1000))

Insert Into @Original Values(1,'000001')
Insert Into @Original Values(2,'000002/000003')
Insert Into @Original Values(3,'000004/000005/000006')
Insert Into @Original Values(4,'000007')
Insert Into @Original Values(5,'000009')
Insert Into @Original Values(6,'000010')
Insert Into @Original Values(7,'000011/000013')

Declare @Temp Table(Risk_Id Int, Risk_Num VarChar(1000))

Insert Into @Temp (Risk_Id, Risk_Num)
Select Risk_Id, Risk_Num
From   @Original

Declare @Output Table(Risk_Id int, Risk_Num VarChar(6), Primary_Risk_Num VarChar(66))

While Exists (Select * From @Temp)
    Begin
        Insert
        Into     @Output(Risk_Id, Risk_Num, Primary_Risk_Num)
        Select  Risk_Id, Risk_Num, Risk_Num
        From    @Temp
        Where   CharIndex('/', Risk_Num) = 0
        
        Delete
        From    @Temp
        Where    CharIndex('/', Risk_Num) = 0
        
        Insert
        Into     @Output(Risk_Id, Risk_Num, Primary_Risk_Num)
        Select  Risk_Id,
                Left(Risk_Num, CharIndex('/', Risk_Num)-1),
                Right(Risk_Num, CharIndex('/', Reverse(Risk_Num)) - 1)
        From    @Temp
        
        Update    @Temp
        Set        Risk_Num = Left(Risk_Num, Len(Risk_Num) - CharIndex('/', Reverse(Risk_Num)))
    End

Select * from @OUtput Order By Risk_Id, Risk_Num, Primary_Risk_Num

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ousoonerjoe]

I used the example you gave, bborissov and keep coming up with the following error:

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

SELECT c.ClaimID,
		[CoverageName] = LEFT(c.UserDef2, CHARINDEX('/',c.UserDef2) - 1),
       [CoverageLayer] = LEFT(SUBSTRING(c.UserDef2, CHARINDEX('/',c.UserDef2) + 1,8000), CHARINDEX('/',SUBSTRING(c.UserDef2, CHARINDEX('/',c.UserDef2) + 1,8000))-1),
       [CoverageForm] = RIGHT(c.UserDef2, CHARINDEX('/',REVERSE(c.UserDef2))-1)
FROM Claims c

any thoughts?

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[SQLSister]

this happens when you have some records with null data, so the substring won't work onthat data. You might be able to get away with a where clause simply excluding records where userdef2 is null.

"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

I completely forgot about NULLs:

SELECT c.ClaimID,
        [CoverageName] = LEFT(c.UserDef2, CHARINDEX('/',c.UserDef2) - 1),
       [CoverageLayer] = LEFT(SUBSTRING(c.UserDef2, CHARINDEX('/',c.UserDef2) + 1,8000), CHARINDEX('/',SUBSTRING(c.UserDef2, CHARINDEX('/',c.UserDef2) + 1,8000))-1),
       [CoverageForm] = RIGHT(c.UserDef2, CHARINDEX('/',REVERSE(c.UserDef2))-1)
FROM Claims c
WHERE UserDef2 IS NOT NULL

again not tested. Also that code requires that you have at least TWO separators in each record which is not NULL.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[jdaman]

This should do what youre looking for:

DECLARE @a TABLE ( col1 VARCHAR(25) )
INSERT  @a ( col1 )
        SELECT  NULL UNION
        SELECT  'GL' UNION
        SELECT  'GL/PRIMARY' UNION
        SELECT  'GL/PRIMARY/OCCURANCE'


SELECT  col1,
        CASE WHEN CHARINDEX('/', col1) = 0 THEN col1
             ELSE LEFT(col1, ISNULL(NULLIF(CHARINDEX('/', col1), 0), 1) - 1)
        END AS CoverageName,
        CASE WHEN CHARINDEX('/', col1) + CHARINDEX('/', REVERSE(col1)) = LEN(col1) + 1
             THEN RIGHT(col1, ISNULL(NULLIF(CHARINDEX('/', REVERSE(col1)), 0), 1) - 1)
             WHEN CHARINDEX('/', col1) > 0
                  AND CHARINDEX('/', col1) + CHARINDEX('/', REVERSE(col1)) < LEN(col1) + 1
             THEN LEFT(RIGHT(col1, LEN(col1) - CHARINDEX('/', col1)), ISNULL(NULLIF(CHARINDEX('/', RIGHT(col1, LEN(col1) - CHARINDEX('/', col1))), 0), 1) - 1)
        END AS CoverageLayer,
        CASE WHEN CHARINDEX('/', col1) > 0
                  AND CHARINDEX('/', col1) + CHARINDEX('/', REVERSE(col1)) < LEN(col1) + 1
             THEN RIGHT(col1, ISNULL(NULLIF(CHARINDEX('/', REVERSE(col1)), 0), 1) - 1)
        END AS CoverageForm
FROM    @a

 

[gmmastros]

I don't know if this is any better than anything else, but I was playing around a little and thought I would share.

DECLARE @a TABLE ( col1 VARCHAR(25) )
INSERT  @a ( col1 )
        SELECT  NULL UNION
        SELECT  'GL' UNION
        SELECT  'GL/PRIMARY' UNION
        SELECT  'GL/PRIMARY/OCCURANCE'

Select *, 
       Reverse(ParseName(Replace(Reverse(Col1), '/', '.'), 1)) As CoverageName,
       Reverse(ParseName(Replace(Reverse(Col1), '/', '.'), 2)) As CoverageLayer,
       Reverse(ParseName(Replace(Reverse(Col1), '/', '.'), 3)) As CoverageForm
From @a

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Gmmastros-

That was it! Thank you greatly!

It accounts for the NULLS and doesn't care if all fields are present. I appreciate the assist.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.