CODEWITHSUNDEEP
t-sqlsql-server-2008
kanth
kanth

Reputation: 341

one column split to more column sql server 2008?

Table name: Table1

id   name 
1    1-aaa-14 milan road
2    23-abcde-lsd road
3    2-mnbvcx-welcoome street

I want the result like this:

Id   name   name1    name2  
1    1      aaa      14 milan road
2    23     abcde    lsd road
3    2      mnbvcx   welcoome street

Upvotes: 2

Views: 3774

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select T.id,
    substring(T.Name, 1, D1.Pos-1) as Name,
    substring(T.Name, D1.Pos+1, D2.Pos-D1.Pos-1) as Name1,
    substring(T.Name, D2.Pos+1, len(T.name)) as Name2
from Table1 as T
  cross apply (select charindex('-', T.Name, 1)) as D1(Pos)
  cross apply (select charindex('-', T.Name, D1.Pos+1)) as D2(Pos)

Testing performance of suggested solutions

Setup:

create table Table1
(
  id int identity primary key,
  Name varchar(50)
)
go

insert into Table1
select '1-aaa-14 milan road' union all
select '23-abcde-lsd road' union all
select '2-mnbvcx-welcoome street'

go 10000

Result:

enter image description here

Upvotes: 1

Andriy M
Andriy M

Reputation: 77687

The below solution uses a recursive CTE for splitting the strings, and PIVOT for displaying the parts in their own columns.

WITH Table1 (id, name) AS (
  SELECT 1, '1-aaa-14 milan road' UNION ALL
  SELECT 2, '23-abcde-lsd road' UNION ALL
  SELECT 3, '2-mnbvcx-welcoome street'
),
cutpositions AS (
  SELECT
    id, name,
    rownum = 1,
    startpos = 1,  
    nextdash = CHARINDEX('-', name + '-')
  FROM Table1
  UNION ALL
  SELECT
    id, name,
    rownum + 1,
    nextdash + 1,
    CHARINDEX('-', name + '-', nextdash + 1)
  FROM cutpositions c
  WHERE nextdash < LEN(name)
)
SELECT
  id,
  [1] AS name,
  [2] AS name1,
  [3] AS name2
  /* add more columns here */
FROM (
  SELECT
    id, rownum,
    part = SUBSTRING(name, startpos, nextdash - startpos)
  FROM cutpositions
) s
PIVOT ( MAX(part) FOR rownum IN ([1], [2], [3] /* extend the list here */) ) x

Without additional modifications this query can split names consisting of up to 100 parts (that's the default maximum recursion depth, which can be changed), but can only display no more than 3 of them. You can easily extend it to however many parts you want it to display, just follow the instructions in the comments.

Upvotes: 1

Allan W
Allan W

Reputation: 590

This function ought to give you what you need.

--Drop Function Dbo.Part
Create Function Dbo.Part
    (@Value Varchar(8000)
    ,@Part Int
    ,@Sep Char(1)='-'
)Returns Varchar(8000)
As Begin
Declare @Start Int
Declare @Finish Int
Set @Start=1
Set @Finish=CharIndex(@Sep,@Value,@Start)
While (@Part>1 And @Finish>0)Begin
    Set @Start=@Finish+1
    Set @Finish=CharIndex(@Sep,@Value,@Start)
    Set @Part=@Part-1
End
If @Part>1 Set @Start=Len(@Value)+1 -- Not found
If @Finish=0 Set @Finish=Len(@Value)+1 -- Last token on line
Return SubString(@Value,@Start,@Finish-@Start)
End

Usage:

Select ID
      ,Dbo.Part(Name,1,Default)As Name
      ,Dbo.Part(Name,2,Default)As Name1
      ,Dbo.Part(Name,3,Default)As Name2
  From Dbo.Table1

It's rather compute-intensive, so if Table1 is very long you ought to write the results to another table, which you could refresh from time to time (perhaps once a day, at night).

Better yet, you could create a trigger, which automatically updates Table2 whenever a change is made to Table1. Assuming that column ID is primary key:

Create Table Dbo.Table2(
    ID Int Constraint PK_Table2 Primary Key,
    Name Varchar(8000),
    Name1 Varchar(8000),
    Name2 Varchar(8000))
Create Trigger Trigger_Table1 on Dbo.Table1 After Insert,Update,Delete
As Begin
If (Select Count(*)From Deleted)>0
    Delete From Dbo.Table2 Where ID=(Select ID From Deleted)
If (Select Count(*)From Inserted)>0
    Insert Dbo.Table2(ID, Name, Name1, Name2)
    Select ID
          ,Dbo.Part(Name,1,Default)
          ,Dbo.Part(Name,2,Default)
          ,Dbo.Part(Name,3,Default)
      From Inserted
End

Now, do your data manipulation (Insert, Update, Delete) on Table1, but do your Select statements on Table2 instead.

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 135021

if you always will have 2 dashes, you can do the following by using PARSENAME

--testing table
CREATE TABLE #test(id INT, NAME VARCHAR(1000))


INSERT #test VALUES(1, '1-aaa-14 milan road')
INSERT #test VALUES(2, '23-abcde-lsd road')
INSERT #test VALUES(3, '2-mnbvcx-welcoome street')

SELECT id,PARSENAME(name,3) AS name,
PARSENAME(name,2) AS name1,
PARSENAME(name,1)AS name2
 FROM (
SELECT id,REPLACE(NAME,'-','.') NAME
FROM #test)x

if you have dots in the name column you have to first replace them and then replace them back to dots in the end

example, by using a tilde to substitute the dot

 INSERT #test VALUES(3, '5-mnbvcx-welcoome street.')


SELECT id,REPLACE(PARSENAME(name,3),'~','.') AS name,
REPLACE(PARSENAME(name,2),'~','.')  AS name1,
REPLACE(PARSENAME(name,1),'~','.') AS name2
 FROM (
SELECT id,REPLACE(REPLACE(NAME,'.','~'),'-','.') NAME
FROM #test)x

Upvotes: 0

Related Questions