SQL SERVER – T-SQL Script to Devide One Column into Two Column

Just a day ago, we faced situation where one column in database contained two values which were separated by comma. We wanted to separate this two values in their own columns. It was interesting that value of the column was variable and something dynamic needed to be written.

Following is quick script which separates one column into two columns. The separate between two values in comma.

CREATE TABLE EMP_Demo
(EMP_PAY VARCHAR(20),
EMP_NAME VARCHAR(20),
PAY_SCALE VARCHAR(20));
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('ALPESH,7009')
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('KRUTI,9909')
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('TANMAY,16000.7')
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('NESHA,6060.8')
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('DEVANG,14000')
UPDATE EMP_Demo
SET EMP_NAME = LEFT(EMP_PAY,CHARINDEX(',',EMP_PAY)-1)
UPDATE EMP_Demo
SET PAY_SCALE = RIGHT(EMP_PAY,LEN(EMP_PAY)-CHARINDEX(',',EMP_PAY))
SELECT *
FROM EMP_Demo
DROP TABLE EMP_Demo
GO


Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

18 thoughts on “SQL SERVER – T-SQL Script to Devide One Column into Two Column

  1. Great job, i am learning the sql server and a big fan of the person who are doing good job in the programming field

  2. Hi,
    good demo of CHARINDEX.

    I want to add a column ‘DEPT’ after ‘EMP_NAME’ column before ‘PAY_SCALE’ in EMP_Demo table.

    I want to add it using SQL statement in 2005.

    how to do it?
    Thanks a lot in advance.

  3. Hea thanks a million love your site.
    What do you do with this query if you have more then two colunms of data though?>

    I’m trying to implement this when using address.
    So i never know how many rows would be needed. but
    say could have 4 or 5 or 6 bits of data seperated by each coma that i would like to see in one cell.
    ‘No 10, Drumhaughley, Killoe, Co. Longford’ For example?

    thanks

  4. @Eibhlin

    This could be easily implemented.

    I do have a question, lets look an example.

    This is your format,

    Apt NO, Building NO, Street Address, City Name, State, Zipcode

    Ex: Apt #14,5233,Niagara falls,NewYork City, New York,12345

    If format is like above, There is no issue, I can easily place all the 6 values in 6 different columns, but what if we get this value,

    5233,Niagara falls,NewYork City, New York,12345

    How I am I Suppose to know ? that there is no Apt No in above example, I might start putting values in wrong column ?

    How do you deal with this, Like if you dont have any value, do you display it as empty, like this,

    ,5233,Niagara falls,NewYork City, New York,12345

    Solution :
    Check this link :

    http://dhameliya.blogspot.com/2009/04/how-to-split-comma-separated-string-in.html

    ~ IM.

  5. hi friends,

    i have one table is normal column is bit

    id isnormal

    1 0
    2 0
    3 0
    4 1
    5 1
    6 0
    7 1

    i wnat oput put like this

    id normal abnormal
    1 0 1
    2 0 1
    3 0 1
    4 1 0
    5 1 0
    6 0 1
    7 1 0

    note:–where isnormal is 0 that time i wnat to display 1 in abnormal column
    where isnormal is 1 that time i wnat to display 1 in normal column
    first i want to divide is normal column into two columns one is
    norman
    second one is
    abnormal

    please help me it’s very urgent in my project

    Thanks & Regards’
    PrasadGopathi

    • Hi Prasad,

      CREATE TABLE status(id INT, isnormal INT, normal INT,abnormal INT)
      GO
      INSERT INTO status(id , isnormal)
      SELECT 1,0
      UNION ALL
      SELECT 2,0
      UNION ALL
      SELECT 3,0
      UNION ALL
      SELECT 4,1
      UNION ALL
      SELECT 5,1
      UNION ALL
      SELECT 6,0
      UNION ALL
      SELECT 7,1

      SELECT * FROM status

      UPDATE status
      SET normal = (isnormal)

      UPDATE status
      SET abnormal = (1-isnormal)

      SELECT id,normal,abnormal FROM status

      I think It is your answer.

      Thanks And Regards
      Rahul Bhargava

    • Hi Prasad
      here is the solution

      declare @t table (id int identity,Normal bit)
      insert into @t values(1),(1),(0),(1),(0),(1),(1)

      Select *,case when Normal=1 then 0
      when Normal=0 then 1 end as Abnormal from @t

  6. @prasad

    The clearest solution is to use a CASE statement.

    WITH
    Data(id, isnormal)
    AS
    (
    SELECT 1, 0 UNION ALL
    SELECT 2, 0 UNION ALL
    SELECT 3, 0 UNION ALL
    SELECT 4, 1 UNION ALL
    SELECT 5, 1 UNION ALL
    SELECT 6, 0 UNION ALL
    SELECT 7, 1
    )
    SELECT
    Id,
    IsNormal,
    CASE IsNormal
    WHEN 0 THEN 1
    ELSE 0
    END Abnormal
    FROM
    Data;

    Though, a mathematical solution could also be used, but not be as clear. Here is one: (IsNormal + 1) % 2

    • Another method would be

      WITH
      Data(id, isnormal)
      AS
      (
      SELECT 1, 0 UNION ALL
      SELECT 2, 0 UNION ALL
      SELECT 3, 0 UNION ALL
      SELECT 4, 1 UNION ALL
      SELECT 5, 1 UNION ALL
      SELECT 6, 0 UNION ALL
      SELECT 7, 1
      )
      SELECT
      Id,
      IsNormal, Isnormal^1 Abnormal
      FROM
      Data;

  7. I need to create add some values in table. where only column value is going to be changed . is there a way to add multiple rows in single oracle statment

    for an instance I have table like

    Company ,Area, EmployeeNo

    In this table company and area field is going to be same and only employee No is going tobe changed and I have employee no in the below format

    1234,4567,7890

    and I am expexting an out put as below

    Company Area Employee No

    Abc US 1234
    Abc US 4567
    Abc US 7890

    is there a way to execute in single statment.

    • Hi Vineesh

      Your solution is as follows

      declare @t table (Company nvarchar(100),Area nvarchar(100), EmployeeNo int)
      insert into @t values(‘Abc’,’US’,Null),(‘Abc’,’US’,Null),(‘Abc’,’US’,Null)

      Select * from @t
      ———-=====================================================================
      declare @Str nvarchar(100)=’1234,4567,7890′,@D char=’,’,@Pos1 int,@Pos2 int=1

      set @Str=@Str+@D
      Set @Pos1=charindex(@D,@Str,1)

      declare @t1 table(id int identity,Number int)

      while @Pos1>0
      ————-Spliting Numbers saperated by comma
      begin

      insert into @t1
      Select Substring(@Str,@Pos2,@Pos1-@Pos2)
      set @Pos2=@Pos1+1
      set @Pos1=charindex(@D,@Str,@Pos1+1)

      end

      ———-======================================================================

      update A
      set A.EmployeeNo=B.Number
      from
      (Select row_number() over(order by Company)RNK,Company,Area,EmployeeNo from @t)A
      inner join @t1 B
      on A.RNK=B.id

      Select * from @t

  8. Hello Sir

    In Sql table i have a column name ‘Course’ in this column has many courses like B.teach,MCA,MBA etc seperated by commas..sir i have to read one value from the column which has multiple values..so plz give me guideline…
    thank you for reply

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s