Parse for unique values - 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

Transact-SQL (2000)

Parse for unique values

Author

Topic

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2006-12-18 : 14:50:59

I have a table that looks like the following:

T1:
id---Value
1----10 10 14 20
2----24 24 30 58
3----20 26 26 30

I want to parse the column "Value" to give me the unique numbers to prevent duplicate numbers showing.

(I.e. Return the following:

id---Value
1----10 14 20
2----24 30 58
3----20 26 30
)

Any help as always is greatly appreciated. Happy Holidays!!

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-18 : 15:11:12

are the values separated each by tab or one space???

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2006-12-18 : 15:18:40

The values are separated each by 1 space.

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-18 : 15:22:56

If the data was normalized it would be a snap

But you most likely are going to have top write a udf to do this

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-18 : 15:25:08

What does SELECT MAX(LEN([value)) FROM Table

Give you?

I just thought of something, but it's definetly a hack at best

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2006-12-18 : 15:35:23

Unfortunately this data came from raw data from a flat file. Those numbers are sequence identifiers and some of the sequences happen more than once. When I run SELECT MAX(LEN([value)) FROM T1 it returns the value "11". Thanks again for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 16:01:28

Are there only 2-digit numbers?

Peter Larsson
Helsingborg, Sweden

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2006-12-18 : 16:03:38

Yes there are only 2-digit numbers in the values column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 16:16:59

[code]-- prepare test data
declare @t table (id tinyint, value varchar(20))

insert @t (id, value)
select 1, '10 10 14 20' union all
select 2, '24 24 30 58' union all
select 3, '19 24 24' union all
select 4, '26 26'

-- stage the data
declare @s table (id tinyint, part varchar(20), p tinyint)

insert @s (id, part)
select id, substring(value, 1, 2) from @t a union
select id, substring(value, 4, 2) from @t a union
select id, substring(value, 7, 2) from @t a union
select id, substring(value, 10, 2) from @t a

update s
set s.p = (select count(*) from @s q where q.id = s.id and q.part <= s.part)
from @s s

-- do the work
select id,
ltrim(rtrim(max(case when p = 1 then part else '' end) + ' ' +
max(case when p = 2 then part else '' end) + ' ' +
max(case when p = 3 then part else '' end))) newcolumnvalue
from @s
group by id[/code]
Peter Larsson
Helsingborg, Sweden

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2006-12-18 : 16:55:23

This works great!! Thanks so much for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources