Like query from another table

Question

Post reply

Like query from another table

Dhruvesh Shah

SSCrazy

Points: 2303
More actions
May 9, 2012 at 6:40 pm

#260731

Hi,
I have a table Account with two columns Ac_no and code_list.
Ac_no Code_list
12345abc cab yac 3ga bc4
43233yub tab yac bc4
34564qwe cab yac 3ga bc4
25864b87 tab ecc 3ga bc4
I have another table code with valid billable codes
billing_code
cab
tab
yac
ecc
How can i use like query (any other) to find out which Ac_no has billing_code within code_list column?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

With 527 posts and 166 visits, you sure would have known how to post a question. Not good.

Please read this article on how to post your question --> How to post questions[/url]

Now, for your benefit, here is the sample data set-up

declare @account table

(

Ac_no int

,Code_list varchar(100)

)

insert into @account( Ac_no, Code_list)

select 12345 , 'abc cab yac 3ga bc4'

union all select 43233,'yub tab yac bc4'

union all select 34564,'qwe cab yac 3ga bc4'

union all select 25864,'b87 tab ecc 3ga bc4'

union all select 67890,'xyz wee tee yee uee'

declare @BillableCodes table

(

billing_code varchar(100)

)

insert into @BillableCodes( billing_code)

select 'cab'

union all select 'tab'

union all select 'yac'

union all select 'ecc'

Here is the t-sql code:

select *

from @account A

cross apply @BillableCodes b

where a.Code_list like '%'+ b.billing_code +'%'

order by a.Ac_no

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

If you don't want duplicates in ColdCoffee's suggestion:

-- Replace:

SELECT *

-- With:

SELECT DISTINCT Ac_no, Code_list

Or, another alternative with a cheaper execution plan cost would be this (also eliminates duplicates):

SELECT *

FROM @account a

WHERE EXISTS(

SELECT billing_code

FROM @BillableCodes b

WHERE a.Code_list like '%'+ b.billing_code +'%')

If you want only the Ac_no where all billing codes are in the @BillableCodes table though, that is an entirely different (more challenging) kettle of fish!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Sony Francis @EY SSCommitted Points: 1539 More actions · Answer 3

I think the query which posted by ColdCoffee is not working

Suppose i want to insert Account Table with

insert into @account( Ac_no, Code_list)

select 9999, 'abcd dcab dyac d3ga dbc4'

Here no codelist is matched against @BillableCodes. But your query return it matches.

Sony Francis @EY SSCommitted Points: 1539 More actions · Answer 4

Dear Dhruvesh Shah ,

Try this

CREATE TABLE #Account

(

Ac_no int

,Code_list varchar(100)

)

insert into #Account( Ac_no, Code_list)

select 12345 , 'abc cab yac 3ga bc4'

union all select 43233,'yub tab yac bc4'

union all select 34564,'qwe cab yac 3ga bc4'

union all select 25864,'b87 tab ecc 3ga bc4'

union all select 67890,'xyz wee tee yee uee'

union all select 9999 , 'abcd dcab dyac d3ga dbc4'

CREATE TABLE #BillableCodes

(

billing_code varchar(100)

)

insert into #BillableCodes( billing_code)

select 'cab'

union all select 'tab'

union all select 'yac'

union all select 'ecc'

select *

from #Account A

cross apply #BillableCodes b

where Replace(a.Code_list,' ','*') like '%'+ '*'+b.billing_code +'*'+'%'order by a.Ac_no

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Sony Francis @EY (5/10/2012)
I think the query which posted by ColdCoffee is not working
Suppose i want to insert Account Table with
insert into @account( Ac_no, Code_list)
select 9999, 'abcd dcab dyac d3ga dbc4'
Here no codelist is matched against @BillableCodes. But your query return it matches.

The OP did not say that the billings codes in the Account tbale is a space-delimited string. IF thats the case, then there are other high performing solutions available.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Sony Francis @EY (5/10/2012)
Dear Dhruvesh Shah ,
Try this
CREATE TABLE #Account
(
Ac_no int
,Code_list varchar(100)
)
insert into #Account( Ac_no, Code_list)
select 12345 , 'abc cab yac 3ga bc4'
union all select 43233,'yub tab yac bc4'
union all select 34564,'qwe cab yac 3ga bc4'
union all select 25864,'b87 tab ecc 3ga bc4'
union all select 67890,'xyz wee tee yee uee'
union all select 9999 , 'abcd dcab dyac d3ga dbc4'
CREATE TABLE #BillableCodes
(
billing_code varchar(100)
)
insert into #BillableCodes( billing_code)
select 'cab'
union all select 'tab'
union all select 'yac'
union all select 'ecc'
select *
from #Account A
cross apply #BillableCodes b
where Replace(a.Code_list,' ','*') like '%'+ '*'+b.billing_code +'*'+'%'order by a.Ac_no

Add this

insert into @account( Ac_no , Code_list)

select 88888, 'abcd *cab* cbc d3ga dbc4'

to your sample data. Should this row be returned or not?

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 7

Thanks All,

It works. Yes all the codes are separated by spaces at this stage but in future they may appear with other delimiter.

I was trying like '%[b.billing_code]%' and it returned unwanted results.

Cheers,

Dhruvesh Shah