Select where Column A like Column B

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

Select where Column A like Column B

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-04 : 12:50:11

Constraint Code Serial Company
FIO SOLUTIONS INC. - QC 342850 CPPOPABAMD ROS WIRELESS PARTNERSHIP INC
FIO SOLUTIONS INC. - QC 342850 CPOPPBBAMD FIO SOLUTIONS INC. 5334

Select * from mytable
where Code = '342850'
and [Constraint] like '%' + Company '%'

I expect to get the second record, but it doesn't show anything. ???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:54:06

it should be

Select * from mytable
where Code = '342850'
and Company like '%' + [Constraint] + '%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-04 : 13:11:27

Thanks visakh16 for trying to help.
But this does not work.
If I put "not like" the I get the 2 records.

quote:
Originally posted by visakh16

it should be

Select * from mytable
where Code = '342850'
and Company like '%' + [Constraint] + '%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:13:48

can you post clearly how data exists? the format is a little weird in first post so i cant make out clearly what data is for which column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-04 : 13:39:20

Here It is the exact query.


SELECT CASE WHEN NPANNX.[Constraint] IS NULL 
	   THEN NULL 
	   WHEN LEN(Ltrim(NPANNX.[Constraint])) < 8 
	   THEN [Constraint] 
	   WHEN IsNumeric(RIGHT(RTrim(NPANNX.[Constraint]), 4)) = 1 
	   THEN substring(LTrim(NPANNX.[Constraint]), 1, Len(LTrim(NPANNX.[Constraint])) - 5) 
	   ELSE [Constraint] 
	   END AS [Constraint],
	   NPANNX.NPA + '-' + NPANNX.NNX AS NPANNXID, 
       viewTheBigPicture.RemoteCLLI,
       viewTheBigPicture.ResultingNPANXXCompany 
FROM   NPANNX INNER JOIN
       viewTheBigPicture ON NPANNX.NPANNXID = viewTheBigPicture.NPANNXID
       WHERE NPANNX.NPA  + '-' + NPANNX.NNX = '418-850'
       AND LTrim(RTrim(ResultingNPANXXCompany))  like '%' + [Constraint] + '%'

quote:
Originally posted by visakh16

can you post clearly how data exists? the format is a little weird in first post so i cant make out clearly what data is for which column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:40:42

i want sample data not the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-04 : 13:44:20

If I put "Not like" instead of "like", I get the following;

Constraint NPANNXID RemoteCLLI ResultingNPANXXCompany
FO SOLUTIONS INC. - QC 418-850 CPSNPQABAMD ROS WIRELESS PARTNERSHIP INC
FO SOLUTIONS INC. - QC 418-850 CPSNPQABBMD FO SOLUTIONS INC. 5643

quote:
Originally posted by visakh16

i want sample data not the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:46:25

please make format correct (may be separate columns by |) i cant make out end and begin of each column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-04 : 13:49:31

quote:
Originally posted by visakh16

please make format correct (may be separate columns by |) i cant make out end and begin of each column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:56:49

then where should be


WHERE NPANNX.NPA  + '-' + NPANNX.NNX = '418-850'
       AND LTrim(RTrim(ResultingNPANXXCompany))  like '%' + LTRIM(RTRIM(LEFT([Constraint],CHARINDEX('-',[Constraint])-1))) + '%'

i hope you always have separator -

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-04 : 13:59:07

The records are not LIKE each other, so I would not expect to get any rows returned. Can you describe your desired behavior in more detail?

I can only assume that you might want to compare the data in the Constraint column up to the first space (or something) to the data in the Company column. But, if you can clarify, we can help you out.

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-04 : 13:59:45

You are amazing!
I wonder why it did not work with my method, can you explain please?
Thanks!

quote:
Originally posted by visakh16

then where should be

WHERE NPANNX.NPA + '-' + NPANNX.NNX = '418-850'
AND LTrim(RTrim(ResultingNPANXXCompany)) like '%' + LTRIM(RTRIM(LEFT([Constraint],CHARINDEX('-',[Constraint])-1))) + '%'

i hope you always have separator -

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 14:00:46

it didnt work because you were looking for whole string while only part of that matches in each case. thats why i took only matching part from your field using LEFT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-04 : 14:07:36

I modified a bit your suggestion by this:
WHERE replace(ResultingNPANXXCompany,'-','') like '%' + Replace([Constraint],'-','') + '%'

This way it works even when there is no '-' in the data.

Thanks!

quote:
Originally posted by infodemers

You are amazing!
I wonder why it did not work with my method, can you explain please?
Thanks!
quote:
Originally posted by visakh16

then where should be

WHERE NPANNX.NPA + '-' + NPANNX.NNX = '418-850'
AND LTrim(RTrim(ResultingNPANXXCompany)) like '%' + LTRIM(RTRIM(LEFT([Constraint],CHARINDEX('-',[Constraint])-1))) + '%'

i hope you always have separator -

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 14:13:33

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources