strip where _ and remove duplicates

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 2008 Forums

Transact-SQL (2008)

strip where _ and remove duplicates

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2011-10-13 : 08:52:36

Hi folks,

I really have tried with this one but I can't get it done.

I have a table where I want to first of all remove characters where there is an underscore on the field and anything after. Below in red is what I would remove. 01 and 03 are the suffixes after the underscore

0243526_01
37617819_01 <duplicate when _01 removed
37617810_03 <duplicate when _03 removed
373927274-101

Secondly I want to remove the duplicates. Here are the conditions
1.Only remove a duplicate row where the suffix is 03
2.Most rows that have 03 also have 01. However, some only have 03 and I don't want to remove those rows (only if there is both 01 & 03)

I can remove duplicates like this
LEFT(field, CHARINDEX('_',field)-1)

I get an error on codes that are like this (maybe something to do with the hyphen):
0370001504-101

The error is this
Invalid Lengh parameter passed to the LEFT SUBSTRING function

Summary:
I just want to remove any rows with the suffix _03 where there are two rows with the same value i.e.
37617810_01
37617810_03

Hope someone can help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-13 : 09:05:07

when you said "remove the duplicate", do you mean delete those rows from the table ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-13 : 09:14:58

quote:


0243526_01
37617819_01 <duplicate when _01 removed
37617810_03 <duplicate when _03 removed
373927274-101

why line 2 & 3 is duplicate when the suffix is removed ?

KH
[spoiler]Time is always against us[/spoiler]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-13 : 09:30:36

assuming you have an ID column


create table #t (i int identity(1,1), val varchar(100))

insert into #t(val)
values
('0243526_01'),
('37617810_01'), 
('37617810_03'), 
('373927274-101')

select * from #t


delete
	y
FROM
	#t y
INNER JOIN
(select 
	i,val,ROW_NUMBER() over(partition by LEFT(val,CHARINDEX('_',val)-1) order by i) as RN
	
from #t
	where
CHARINDEX('_',val)-1 > -1
) x
ON
	y.i = x.i
where 
	x.RN = 2

select * from #t

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources