CODEWITHSUNDEEP
sqlsql-server
user406151
user406151

Reputation: 425

Split a column using delimiter

I have a column with values like AAA-BBB-CCC or AAA-BBB-CCC-DDD.

I am only interested in splitting and retrieving AAA BBB CCC.

I've tried using PARSENAME, however that messes up.

Upvotes: 0

Views: 233

Answers (3)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1
    ([value] varchar(50))
;

INSERT INTO Table1
    ([value])
VALUES
    ('AAA-BBB-CCC'),
    ('AAA-BBB-CCC-DDD'),
    ('AAAAA-BBBB-CCCC'),
    ('AAAAA-BBBB-CCCCCC-DDD'),
    ('AAAAAA-BBB-CCC'),
    ('AAAAAAAA-BBBBBB-CCCCCC-DDD-EEEE')

;

Query 1:

SELECT replace(
          substring(value,1,(
               case when charindex('-',value,charindex('-',value,charindex('-',value)+1)+1) = 0 
                    then len(value)
                    else charindex('-',value,charindex('-',value,charindex('-',value)+1)+1)-1 end
          )),'-',' ')
FROM Table1

Results:

|               COLUMN_0 |
|------------------------|
|            AAA BBB CCC |
|            AAA BBB CCC |
|        AAAAA BBBB CCCC |
|      AAAAA BBBB CCCCCC |
|         AAAAAA BBB CCC |
| AAAAAAAA BBBBBB CCCCCC |

OR

SQL Fiddle

Query 2:

SELECT value,
       PARSENAME(replace(
          substring(value,1,(
               case when charindex('-',value,charindex('-',value,charindex('-',value)+1)+1) = 0 
                    then len(value)
                    else charindex('-',value,charindex('-',value,charindex('-',value)+1)+1)-1 end
          )),'-','.'),3) as '1st',
       PARSENAME(replace(
          substring(value,1,(
               case when charindex('-',value,charindex('-',value,charindex('-',value)+1)+1) = 0 
                    then len(value)
                    else charindex('-',value,charindex('-',value,charindex('-',value)+1)+1)-1 end
          )),'-','.'),2) as '2nd',
       PARSENAME(replace(
          substring(value,1,(
               case when charindex('-',value,charindex('-',value,charindex('-',value)+1)+1) = 0 
                    then len(value)
                    else charindex('-',value,charindex('-',value,charindex('-',value)+1)+1)-1 end
          )),'-','.'),1) as '3rd'
FROM Table1

Results:

|                           VALUE |      1ST |    2ND |    3RD |
|---------------------------------|----------|--------|--------|
|                     AAA-BBB-CCC |      AAA |    BBB |    CCC |
|                 AAA-BBB-CCC-DDD |      AAA |    BBB |    CCC |
|                 AAAAA-BBBB-CCCC |    AAAAA |   BBBB |   CCCC |
|           AAAAA-BBBB-CCCCCC-DDD |    AAAAA |   BBBB | CCCCCC |
|                  AAAAAA-BBB-CCC |   AAAAAA |    BBB |    CCC |
| AAAAAAAA-BBBBBB-CCCCCC-DDD-EEEE | AAAAAAAA | BBBBBB | CCCCCC |

Upvotes: 2

Eduard Uta
Eduard Uta

Reputation: 2607

One solution would be using replace function like so:

select replace(columnName, '-', ' ') as SplittedVal

If only AAA BBB CCC is needed and the values are formated like 3 characters space 3 characters space 3 characters (so 11 characters in total) then use:

select left(replace(columnName, '-', ' '), 11)

Upvotes: 1

Incognito
Incognito

Reputation: 3074

If I understand this correctly, your only intention is to replace - with a whitespace. IF that is so, just use REPLACE function.

SELECT REPLACE('abcdefghicde','cde','xxx');
do

SO, your code will be 

SELECT REPLACE('AAA-BBB-CCC','-',' ');
do

Upvotes: 0

Related Questions