creating new column from existing column using t-sql

Question

i am trying to create new column from existing column(table) in a new table.

this is my old table

projectnum       allw     budjet
648PE2075       152.00     230.00
648PE2075A      33.33      00.00
333AD0221B       125.11    1256.00
123CF0023        125.22     215.33

I need to create a new table that have a new column called Project_code created from projectnum column and all the old columns. looks like this

projectnum   Project_code      allw       budjet
648PE2075       648-075       152.00     230.00
648PE2075A      648-075-A      33.33      00.00
333AD0221B      333-221-B      125.11    1256.00
123CF0023        123-023       125.22     215.33

My challeng is when i try to write t_sql statement. some records of projectnum are 10 character rest 9 character. Help Please

the middle 2 letters and 1 number ( characters in 4,5,6 places)have to be excluded or replaced by "-" and if the projectnum have a letter @ 10th position there must be - (hiven) in between. — zazzu
– zazzu, Commented May 14, 2014 at 18:26

timo.rieber · Accepted Answer · 2014-05-14 18:26:52Z

1

I suggest using select ... into ... from ... to create a new table from existing data in one step. For your string operations substring() seems appropriate. Please try the following query:

select
    projectnum,
    allw,
    budjet,
    substring(projectnum, 1, 3)
        + '-'
        + substring(projectnum, 7, 3)
        + case
            when len(projectnum) = 10
            then '-' + substring(projectnum, len(projectnum) - 1, 1) end
        as project_code

into
    new_table

from
    old_table

Read more on substring() at the Microsoft Docs.

answered May 14, 2014 at 18:26

timo.rieber

3,8773 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zazzu Over a year ago

Thank You all. learn a lot and got exactly what i want. Thank you so much for your fast respons. every day is a learning day for me :)

Alan Schofield · Accepted Answer · 2014-05-14 18:22:44Z

0

SELECT
    projectnum,
    CASE LEN(projectnum) 
        WHEN 9 THEN LEFT(projectnum,3) + '-' + SUBSTRING(projectnum,6,3)
        WHEN 10 THEN LEFT(projectnum,3) + '-' + SUBSTRING(projectnum,6,3) + '-' + RIGHT(projectnum,1)
    END AS Project_code,
    allw,
    budjet
INTO MyNewTable
FROM MyOldTable

Just obviously swap the table names! This will create the new table too, if you already have the table just change it so it reads

INSERT INTO MyNewTable(projectnum,Project_code, allw, budjet)
SELECT
    projectnum,
    CASE LEN(projectnum) 
        WHEN 9 THEN LEFT(projectnum,3) + '-' + SUBSTRING(projectnum,6,3)
        WHEN 10 THEN LEFT(projectnum,3) + '-' + SUBSTRING(projectnum,6,3) + '-' + RIGHT(projectnum,1)
    END AS Project_code,
    allw,
    budjet
FROM MyOldTable

There are much more elegant solutions that would allow for more options but hopefully this will work or give you an idea how to solve any other similar issues.

Al.

answered May 14, 2014 at 18:22

Alan Schofield

21.9k3 gold badges25 silver badges38 bronze badges

1 Comment

zazzu Over a year ago

Thank You all. learn a lot and got exactly what i want. Thank you so much for your fast respons. every day is a learning day for me :)

Gary Walker · Accepted Answer · 2014-05-14 19:35:46Z

0

Make it reusable logic as a function

create function code(@num varchar(20)) returns varchar(20) as
begin    
     return substring(@num, 1, 3) + '-' + substring(@num, 7, 3)
       + case when len(@num) = 10 then '-' + substring(@num, 10, 1) else '' end
end

the use as needed

select dbo.code('648PE2075')
select dbo.code('648PE2075A')

You can inline it when needed as it will be faster than a UDF call.

ADDED

If you have lots of rows, in-line is still the faster, but a table return udf is mostly fast and still resable

e.g.

create function tblcode(@num varchar(20)) returns table as
   return select substring(@num, 1, 3) + '-' + substring(@num, 7, 3)
   + case when len(@num) = 10 then '-' + substring(@num, 10, 1) else '' end as code

and use it like

select * 
from ( select D.* from T.ProjectNum
cross apply dbo.tblcode(T.ProjectNum)
) as xx
cross apply dbo.tblcode(xx.project)

edited May 14, 2014 at 19:35

answered May 14, 2014 at 18:35

Gary Walker

9,2043 gold badges22 silver badges43 bronze badges

1 Comment

zazzu Over a year ago

but i want to insert a lot of records all at once in a new column.

Collectives™ on Stack Overflow

creating new column from existing column using t-sql

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related