Convert Field Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Convert Field Value

Author

Topic

SimonG
Starting Member

15 Posts

Posted - 2010-04-20 : 12:15:53

Hi all,

New to DTS so bear with me.

I have a table with a field contract number. I would like to parse the table and change the contract number value in the table (or write the value to another column) based on the numeric portion of the contract number

For example the contract number may have the values C£0343, CW0343 and CW0343-01. I'd like to change these values to C0343.

Is this possible?

Thanks in advance.

Simon

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-20 : 14:39:28

This looks like you just need an update statement and no dts or you should be more clear.
To give an advice on the update statement we need more info about all possible values so we can see what rule will be good to change the data.

No, you're never too old to Yak'n'Roll if you're too young to die.

SimonG
Starting Member

15 Posts

Posted - 2010-04-20 : 17:07:30

Thanks for your post.

The reason I have mentioned DTS is that I am exporting and transforming data from one DB to another using DTS and I would like this process to be part of that data export process.

As far as I am aware the above values are the range of variation I will encounter.

My post in the T-SQL forum (for which I was duly reprimanded by your goodself) was on the basis that I thought it might be possible to created a calculated column on the fly?

Again thanks for your help.

Regards,

Simon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:43:20

can you post what are the rules to be followed for convertion of contract number valueS?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SimonG
Starting Member

15 Posts

Posted - 2010-04-21 : 03:28:15

Thanks for the post.

A little more detail;

I'm using DTS to export to a table called contract sales with columns ContractNumber, Cost, TransactionDate

Our contracts are split but all related sales cost has the rule that the numerical portion within the first six characters is always the same i.e. 0343 in my first post.

the contract number format has three possible value formats;

C0343 - if 5 chars strip first to give 0343
C£0343 - if 6 chars strip first two to give 0343
C$0343-01 - if > 6 chars strip first two and anything after sixth to give 0343

I can then use the contract number field for further grouping etc.

Hope this helps.

Simon

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 04:24:27

[code]-- making testdata
declare @test table (id int identity(1,1),ContractNumber varchar(255))
insert @test
select 'C0343' union all
select 'C£0343' union all
select 'C$0343-01'
select * from @test

-- show solution using select
select
id,
ContractNumber,
case len(ContractNumber)
when 5 then stuff(ContractNumber,1,1,'')
when 6 then stuff(ContractNumber,1,2,'')
else left(stuff(ContractNumber,1,2,''),4)
end as NewContractNumber
from @test

--solution using update
update @test
set ContractNumber=case len(ContractNumber)
when 5 then stuff(ContractNumber,1,1,'')
when 6 then stuff(ContractNumber,1,2,'')
else left(stuff(ContractNumber,1,2,''),4)
end
where len(ContractNumber)>= 5

-- show result
select * from @test
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 05:42:09

Here is the solution by using a computed column:


-- declare table with a computed column
declare @test table(
id int identity(1,1),
ContractNumber varchar(255),
ComputedContractNumber as 
(case len(ContractNumber)
 when 5 then stuff(ContractNumber,1,1,'')
 when 6 then stuff(ContractNumber,1,2,'')
 else left(stuff(ContractNumber,1,2,''),4)
 end))
 
-- insert test-data
insert @test
select 'C0343' union all
select 'C£0343' union all
select 'C$0343-01'

-- see the result
select * from @test