How select with replace - 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)

How select with replace

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-01-15 : 01:59:43

In tbl_detail, it has 5 rows data:

L1A1
L1B1
L1_P1
L1_P2
LA1_A1
LA1_A2

In tbl_ref_line, it has

L1
LA1

I want to select tbl_detail where line exist in tbl_ref_line, with truncate the A1, B1, _P1, _P2, _A1, _A2.

Any idea? Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-15 : 02:22:37

select ref.column from tbl_ref_line as ref inner join tbl_detail as detail
on detail.column like ref.column+'%'
group by ref.column

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 07:28:03

[code]select stuff(d.col,patindex(rl.col+'%',d.col)+ LEN(rl.col),LEN(d.col)-LEN(rl.col),''
from tbl_detail d
inner join tbl_ref_line rl
on d.col like rl.col + '%'
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-15 : 23:29:20

try this
declare @tbl_detail table(col varchar(32))
insert into @tbl_detail
select 'L1A1' union all
select 'L1B1' union all
select 'L1_P1' union all
select 'L1_P2' union all
select 'LA1_A1' union all
select 'LA1_A2' union all
select 'Ld2-A2'

declare @tbl_ref_line table(col varchar(32))
insert into @tbl_ref_line
select 'L1' union all
select 'LA1'

select t.col from @tbl_detail as t inner join
@tbl_ref_line as r on ','+t.col LIKE '%,'+r.col +'%'

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-15 : 23:54:41

declare @tbl_detail table (detid varchar(66))
insert into @tbl_detail
select 'L1A1' union all
select 'L1B1' union all
select 'L1_P1' union all
select 'L1_P2' union all
select 'LA1_A1' union all
select 'LA1_A2'

declare @tbl_ref_line table (refid varchar(66))
insert into @tbl_ref_line
select 'L1' union all
select 'LA1'

select left(detid,len(refid)) as col from @tbl_detail d
inner join @tbl_ref_line r on (d.detid like r.refid+'%')

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources