SQL and string manipulations - 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 2005 Forums

Transact-SQL (2005)

SQL and string manipulations

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-02-03 : 14:03:15

In table A, there is a field called "selectedDistricts" and the value in this field can be any of the following

a. 1
b. 1-2
c. 1-3-5
d 1-2-3-4-5

Where 5 is the maximum number

Now, I want to import this "selectedDistricts" from tableA to tableB. But the trick is want to parse the string, 1-2-3-4-5 into 5 different columns in tableB. Table B has fields District1, District2, District3, District4 and District5

For example:

TableA:selectedDistricts
1-2-5

should transform to

TableB:District1
1

TableB:District2
1

TableB:District3
0

TableB:District4
0

TableB:District5
1

How can I do this in SQL? Please help. Thanks a lot

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-03 : 17:09:13

Here's and example:



declare @tablea table
(	
	selectedDistricts varchar(50)
)

declare @tableb table
(	
	District1 int,
	District2 int,
	District3 int,
	District4 int,
	District5 int
)

insert @tablea 
select '1' union all
select '1-2' union all
select '1-3-5' union all
select '1-2-3-4-5'


insert into @tableb (District1, District2, District3, District4, District5)
select 
	case when charindex('1', selectedDistricts) > 0 then 1 else 0 end as District1,
	case when charindex('2', selectedDistricts) > 0 then 1 else 0 end as District2,
	case when charindex('3', selectedDistricts) > 0 then 1 else 0 end as District3,
	case when charindex('4', selectedDistricts) > 0 then 1 else 0 end as District4,
	case when charindex('4', selectedDistricts) > 0 then 1 else 0 end as District5
from @tablea

select * from @tableb

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 23:54:54

use like operator also
try like this too
insert into @tableb (District1, District2, District3, District4, District5)
select
case when selectedDistricts like '%1%' then 1 else 0 end as District1,
case when selectedDistricts like '%2%' then 1 else 0 end as District2,
case when selectedDistricts like '%3%' then 1 else 0 end as District3,
case when selectedDistricts like '%4%' then 1 else 0 end as District4,
case when selectedDistricts like '%5%' then 1 else 0 end as District5
from @tablea

select * from @tableb

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-02-05 : 09:29:26

Thanks for your input. I actually need to do an update rather than the INSERT. So I came up with the SQL below

UPDATE tableA
SET tableA.District1=
CASE
WHEN tableB.selectedDistricts like '%1'
THEN 1
ELSE 0
END
,tableA.District2=
CASE
WHEN tableB.selectedDistricts like '%2'
THEN 1
ELSE 0
END
,tableA.District3=
CASE
WHEN tableB.selectedDistricts like '%3'
THEN 1
ELSE 0
END
,tableA.District4=
CASE
WHEN tableB.selectedDistricts like '%4'
THEN 1
ELSE 0
END
,tableA.District5=
CASE
WHEN tableB.selectedDistricts like '%5'
THEN 1
ELSE 0
END
FROM tableB
WHERE tableB.RowID = tableA.RowID

but it doesn't work though. What am I doing wrong? PLEASE help.

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-02-05 : 09:32:50

Figured it out. It was missing an "%" after the numbers. It should be '%1%', '%2%' and so forth instead of '%1', '%2'. The sql below worked! Thanks

UPDATE tableA
SET tableA.District1=
CASE
WHEN tableB.selectedDistricts like '%1%'
THEN 1
ELSE 0
END
,tableA.District2=
CASE
WHEN tableB.selectedDistricts like '%2%'
THEN 1
ELSE 0
END
,tableA.District3=
CASE
WHEN tableB.selectedDistricts like '%3%'
THEN 1
ELSE 0
END
,tableA.District4=
CASE
WHEN tableB.selectedDistricts like '%4%'
THEN 1
ELSE 0
END
,tableA.District5=
CASE
WHEN tableB.selectedDistricts like '%5%'
THEN 1
ELSE 0
END
FROM tableB
WHERE tableB.RowID = tableA.RowID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources