SQLTeam.com | Weblogs | Forums

Split values in one column into 3 columns

Hi,
I have an unlimited string in one column with a - as a delimiter. I need to split the values into 3 different columns in the same table.

How can I achieve this without the functions PARSENAME or SPLIT-FUNCTION?

Thanks

DECLARE @Tmp TABLE (empid INT,accountno nchar(50))

INSERT @Tmp SELECT 1,'1990-111-6789'
INSERT @Tmp SELECT 2,'2000-2222-065'

INSERT @Tmp SELECT 3,'1993-3333-2349'

INSERT @Tmp SELECT 4,'99-44-05'
INSERT @Tmp SELECT 5,'1999-555-743'

INSERT @Tmp SELECT 6,'2001-6666-70812 '

The result needs to be something like this
empid accountno part1 part2 part3
4 99-44-05 99 44 05
6 2001-6666-70812 2001 6666 70812

SELECT accountno,
    LEFT(accountno, dash1 - 1) AS part1,
    SUBSTRING(accountno, dash1 + 1, dash2 - dash1 - 1) AS part2,
    SUBSTRING(accountno, dash2 + 1, 50) AS part3
FROM @Tmp
CROSS APPLY (
    SELECT CHARINDEX('-', accountno) AS dash1,
        CHARINDEX('-', accountno, CHARINDEX('-', accountno) + 1) AS dash2
) AS ca1
1 Like