Untrivial task including regular expressions

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)

Untrivial task including regular expressions

Author

Topic

lakeoffm
Starting Member

2 Posts

Posted - 2012-09-21 : 06:46:26

Hi! I have a problem building an SQL script to select from a db.

The data is hyerarcical and is stored in one table. Generaly, we use java to operate a select like this, but here he need to use just the SQL.

So, here is the table:
number branch code
1000 center XXCO
1001 xyz XXER
1002 zyx XXRE
2000 center2 YYCO
2001 xyz YYER
2002 zyx YYRE

All the items that have "CO" in the end of their code are parents to the items that have the same first two symbols in the code.

The task is to get a list of all the branches with their respective parents, like this:
center xyz XXER
center zyx XXRE
center2 xyz YYER
center2 zyx YYRE

Can anybody please give a hand?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 07:05:42

[code]CREATE TABLE #tmp
(n INT, branch VARCHAR(32), code VARCHAR(32));
insert into #tmp values ('1000','center','XXCO');
insert into #tmp values ('1001','xyz','XXER');
insert into #tmp values ('1002','zyx','XXRE');
insert into #tmp values ('2000','center2','YYCO');
insert into #tmp values ('2001','xyz','YYER');
insert into #tmp values ('2002','zyx','YYRE');

SELECT
a.branch,
b.branch,
a.CODE
FROM
#tmp a
INNER JOIN #tmp b ON
LEFT(a.code,2) = LEFT(b.code,2)
AND a.code NOT LIKE '%CO'
AND b.code LIKE '%CO';

DROP TABLE #tmp;[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-21 : 07:13:42

[code]declare @sample table(number int, branch varchar(30), code varchar(30))

insert @sample
select 1000, 'center', 'XXCO' union all
select 1001, 'xyz', 'XXER' union all
select 1002, 'zyx', 'XXRE' union all
select 2000, 'center2', 'YYCO' union all
select 2001, 'xyz', 'YYER' union all
select 2002, 'zyx', 'YYRE'

--All the items that have "CO" in the end of their code are parents to the items that have the same first two symbols in the code.

--The task is to get a list of all the branches with their respective parents, like this:
--center xyz XXER
--center zyx XXRE
--center2 xyz YYER
--center2 zyx YYRE

select * from @sample

select t1.branch,t2.branch,t2.code
from @sample t1
join @sample t2 on left(t2.code,2) = left(t1.code,2) and right(t2.code,2) <> 'CO'
where right(t1.code,2) = 'CO'[/code]

Too old to Rock'n'Roll too young to die.

lakeoffm
Starting Member

2 Posts

Posted - 2012-09-21 : 07:25:39

I mplemented the code, but it gives me an "ORA-00904 Invalid Identifier" error, pointing al the position between the equals sign and the second left..

SELECT b.branch, a.branch, a.code
FROM tmp a JOIN tmp b ON
left(a.code,2) = left(b.code,2)
AND a.code NOT LIKE '%CO' AND b.code LIKE '%CO';

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 07:34:26

The code both Fred and I posted are for SQL Server. This forum is a Microsoft SQL Server forum, so there may be very few people who can answer Oracle questions, if any at all.

You would get better and faster responses at another forum such as dbforums.com.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 07:35:52

quote:
Originally posted by webfred

Too old to Rock'n'Roll too young to die.

Today I am doing a very good job of annoying you, aren't I?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-21 : 08:31:46

quote:
Originally posted by sunitabeck

quote:
Originally posted by webfred

Too old to Rock'n'Roll too young to die.

Today I am doing a very good job of annoying you, aren't I?

You are trying hard but you can't

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources