Conditional substring Insert - 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

General SQL Server Forums

New to SQL Server Programming

Conditional substring Insert

Author

Topic

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-18 : 07:58:40

Hi everyone. I am trying to insert a new to an existing table and I am going round in circles!!!
I want to conditioanlly insert a substring of an existing field; for one value (Ford) insert 5 chars otherwise insert 4. For example...

MAKE CODE SHORT_CODE
FORD 1234567 12345
VW ABC1234 ABC1

I have almost worked out how to do it in a select but cannot get it to update the table!

Sorry, I am sort of asking 3 things at once here!

Many, many thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-18 : 08:08:27

Is this u want check it once
declare @t table(MAKE varchar(12), CODE varchar(12),SHORT_CODE VARCHAR(12))
insert into @t select 'FORD', '1234567',NULL
insert into @t select 'VW', 'ABC1234',NULL

UPDATE t
set short_code = s.short_code
from @t t
inner join (
select make,code,case when MAKE LIKE 'FORD' THEN substring(code,1,5) else substring(code,1,4) end as short_code
from @t ) s on s.make = t.make and s.code = t.code

select * from @t

matty
Posting Yak Master

161 Posts

Posted - 2009-03-18 : 08:08:38

UPDATE table
SET SHORT_CODE = CASE MAKE WHEN 'FORD' THEN LEFT(CODE,5) ELSE LEFT(CODE,4) END

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-18 : 08:23:49

Hi Matty

That works perfectly! This assumes that I have "SHORT_CODE" already set up. If I try and add it does not compile.....

ALTER table Sample_2009
ADD COLUMN Squish VARCHAR(12)
GO
UPDATE Sample_2009
SET Squish = CASE JDP_MAKE WHEN 'FORD' THEN LEFT(VIN,12) ELSE LEFT(VIN,11) END
GO
SELECT * FROM Sample_2009

Any ideas aas to why I cannot add the column?

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-18 : 08:26:53

Please ignore that last post - I am an idiot!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 08:38:47

You can make the column calculated too.

ALTER TABLE	Sample_2009
ADD COLUMN	Squish AS (CASE JDP_MAKE WHEN 'FORD' THEN LEFT(VIN,12) ELSE LEFT(VIN,11) END)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources