Finding dup string pairs in a 10 char string

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

Finding dup string pairs in a 10 char string

Author

Topic

jmikula019
Starting Member

4 Posts

Posted - 2012-06-07 : 11:48:56

I have a list of strings: 1. HEAWAMFWSP 2. TLHHHAFWSP 3. AWAMFWHHAW 4. AUAWAMHHHA . . .

Each of these strings represent 5 pairs of 2 character combinations (i.e. HE AW AM FW SP)

What I am looking to do in SQL is to display all strings that have duplication in the pairs.

Take string number 3 from above. AW AM FW HH AW. I need to display this record because it has a duplicate pair.

Is this possible?

Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 11:51:46

I would be tempted to use a cte to split the string into character pairs then group by having count(*() > 1.
If they are fixed length - i.e. I maximum number then you can just use fixed substrings to get the pairs.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jmikula019
Starting Member

4 Posts

Posted - 2012-06-07 : 11:59:04

Ok.. 2 things I forgot to mention.. The list of character strings are huge.. 9 million records.. And second, I am pretty new to SQL, so I may need a bit of explanation to get this off the ground...

DO you mean something like(ITEM0 being the column name):

WITH BU(HE, SL1, SL2, SL3, SL4) AS
(SELECT substring(ITEM0, 1, 2), substring(ITEM0, 3,2), substring(ITEM0, 5, 2), substring(ITEM0, 7,2), substring(ITEM0, 9,2) FROM $A$ )

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 12:35:23

would be more like

;with cte as
(
select ITEM0, pk = row_number() over (order by ITEMN0) -- use a pk for this if you have one
) ,
cte2 as
(
select pk, s = substring(ITEM0, 1, 2) from cte
union all
select pk, s = substring(ITEM0, 3,2) from cte
union all
select pk, s = substring(ITEM0, 5, 2) from cte
union all
select pk, s = substring(ITEM0, 7,2) from cte
union all
select pk, s = substring(ITEM0, 9,2) from cte
)
select pk
from cte2
group by pk
having count(distinct s) <> 5

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jmikula019
Starting Member

4 Posts

Posted - 2012-06-07 : 12:59:39

OK.. I see where you are getting at.. but this didn't work.. the error I got was:

Error: java.sql.SQLException: ORA-00923: FROM keyword not found where expected : with cte as ( select ITEM0, pk = row_number() over (order by ITEMN0) ) , cte2 as ( select pk, s = substring(ITEM0, 1, 2) from cte union all select pk, s = substring(ITEM0, 3,2) from cte union all select pk, s = substring(ITEM0, 5, 2) from cte union all select pk, s = substring(ITEM0, 7,2) from cte union all select pk, s = substring(ITEM0, 9,2) from cte ) select pk from cte2 group by pk having count(distinct s) <> 5

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 13:33:47

Oracle perchance?
should be - add a from clause to the first cte

;with cte as
(
select ITEM0, pk = row_number() over (order by ITEMN0) from mytable -- use a pk for this if you have one
) ,
cte2 as
(
select pk, s = substring(ITEM0, 1, 2) from cte
union all
select pk, s = substring(ITEM0, 3,2) from cte
union all
select pk, s = substring(ITEM0, 5, 2) from cte
union all
select pk, s = substring(ITEM0, 7,2) from cte
union all
select pk, s = substring(ITEM0, 9,2) from cte
)
select pk
from cte2
group by pk
having count(distinct s) <> 5

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jmikula019
Starting Member

4 Posts

Posted - 2012-06-07 : 13:50:50

Yes Oracle 9i, using a proprietary GUI..

Still throwing the same error..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources