T-SQL: Query Returning No Data When Using LIKE

Is this against SLQ Server?
If not you may try ILIKE instead of LIKE to avoid case sensitivity issues

Not sure what you are searching for
But like has the format  

does the pattern only changes on the end,
What is the significance of the semi-colon?
Can you post a sampledatafromone row that should be returned?

Based on example data from previous questions, it works for me:

create table junk (component varchar(10), lot varchar(15));
insert into junk values('506N','506N418G06A-40');

select * from junk where lot like component+'%';

Select allOpen in new window

Working fiddle here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b2057472365a5dfa8d3c291777614c35

Are the columns VARCHAR or CHAR?

You could try to see if by trimming all spaces from [Component] columns help:

SELECT DISTINCT [LOT]
     FROM WIPTEST
     WHERE 
	   [LOT] LIKE (ltrim(rtrim([Component])))+'%'

Select allOpen in new window

I'm inclined to use your some form of where lot like component+'%'.  But, it's not working.

This is weird.

Any ideas at all?

It works for me in the fiddle I posted.

You didn't answer my question:  What are the data types of those two columns?

For us to provide tested solutions, we will need sample data and expected results.

Please consider posting a sample row that should be displayed.

I don't know what the data types are.  Component comes from another view, while LOT comes from a separate table.  LOT's data type in that table is char(21).  Perhaps, that's what the cause is.  How do I overcome it?

Attached are the Results of running my view, which I'm trying to modify to show where [Component}+'%' LIKE [LOT].

Below is the code of my view.

select DISTINCT [INVHDRBATCH], --[INVHDRDOC], [LOTSEQNBR], 
[FGBATCH], [FG], [FGLABOR], [FGOH], [FGMAT], [FGCOST], [FGWEIGHT], [LABORLB], [OHLB], [MATLB], --[FORMULA], [FORMULAMAT], [FORMULALABOR], [FORMULAOH], 
[Component], [LOT], 
[LOTQTY], [LOTMATUNITCOST], [LOTMATWIPCOST], [LOTLABORWIPCOST],
[LOTOHWIPCOST]
from WIPTEST
INNER JOIN IV30200 on WIPTEST.[INVHDRBATCH] = IV30200.BACHNUMB AND WIPTEST.[INVHDRDOC] = IV30200.DOCNUMBR
LEFT OUTER JOIN FMMAST ON WIPTEST.[Component] = FMMAST.[FormulaId]
where [FG] = '506' and [FGBATCH] = '50418G12D' and [INVHDRBATCH] = '50418G12D'

Select allOpen in new window

Results.xlsx

Or, not use LIKE and test if it exists inside

e.g.

select charindex('abc   ','abcdef') -- = 0  ie a zero result means it doesnt exist

select charindex(rtrim('abc   '),'abcdef') -- = 1 ie test for = 1 if at start of string 'abcdef'

select charindex(rtrim('def   '),'abcdef') -- = 4  ie test for > 1 if anywhere in string 'abcdef'

Select allOpen in new window

does not make sense.if component is from a view while the one you are comparing is from a table, and you have nothing that joins combines the data, how are you looking to match.

don't you get an error that the component is unknown/undefined?

please post the two columns side by side

select top (10) lot,component from wiptest

Select allOpen in new window

Hi All:

Thank you, for the quick responses!

Unfortunately, the issue remains.

The only response that I haven't tested is "cast them as varchar".  Would someone please provide me the syntax, for this?  I don't know it.

Thanks, again!

Software Engineer

cast([lot] as varchar)

or

convert (varchar,[lot])


and if you have a length, you can use that as well for varchar e.g varchar(20)

you might have non visible characters in there as well.

select top (10) lot, len(lot),component,len(component) from wiptest

LOT's data type in that table is char(21).  Perhaps, that's what the cause is.  How do I overcome it?

do you have control over the table or tell the DBA to update the field's data type, let's say from char(21) to such as varchar(21) ? it probably will help you to minimize the issues

Shouldn't have to change the datatype, it might not auto-delete trailing spaces, might need to repopulate ...

Still, havent got to the bottom of it yet. Just to show that CHAR should work, try this example :

Create table #wiptest_char(id int identity, lot char(21), component char(30))

insert #wiptest_char(lot,component) values ('LOT1                 ','LOT1A                   ')
insert #wiptest_char(lot,component) values ('LOT2                 ','LOT 2A                   ')

select lot,component,charindex(rtrim(lot),component),charindex(lot,component) 
from #wiptest_char
where component like rtrim(lot)+'%'

Select allOpen in new window

Cannot recall if LOT is the short one (ie inside component) or COMPONENT is the short one (ie inside lot)

The above code assumes lot is the short string and exists inside of component.... ie one lot = many components

The code below is the other way around.... ie one component = many lot

insert #wiptest_char(lot,component) values ('LOT1A                 ','LOT1                   ')
insert #wiptest_char(lot,component) values ('LOT2A                 ','LOT 2                   ')

select lot,component,charindex(rtrim(component),lot),charindex(component,lot) 
from #wiptest_char
where lot like rtrim(component)+'%'

Select allOpen in new window

>>Component comes from another view, while LOT comes from a separate table<<
Are you saying that the values for the Component and Lot that you are trying to compare don't exist in the WIPTEST view>?

>>Unfortunately, the issue remains.

We cannot see your data or system so just saying it doesn't work really doesn't help us.  If you post sample data that can show us that it isn't working, then we can provide working test cases like the dbfiddle link I posted above.


At times it helps to actually see "spaces" if the exist.

Try:
select ':' + lot + ':', ':' + component + ':' from ...


Taking lot:  If it has 'hello', you should see ':hello:'.  If you see ':                 hello                     :', you have spaces and need to deal with them.

It comes down to:  You need to know your data.  The SQL is pretty easy once you know what you need to do with the data you have.

>>  If you see ':                 hello                     :', you have spaces and need to deal with them.

True, but then your use of TRIM() should resolve spaces. So there is something else...

It could be a range of things including a <tab> or <cr> buried in there - that tends to be the problem when all else fails using LIKE. Then there is case sensitivity, code sets, ansi_padding and a few other possibilities when it becomes seemingly (and always frustratingly) irrational.  Thats in part, why I suggested trying the charindex() and len(), or datalength() to start isolating spaces from the problem, and we can count characters from the select to compare against len()...

So, there is indeed more to it than just code. As you say, the T-SQL should be straight forward. But it isnt working just yet so we need some clues.

One clue with my above requests is to establish the following  - making sure it is the shorter string with the wildcard appended being matched against the longer string  (ignoring the charindex() and rtrim() for now)  :

select 'Found ShortStr in LongStr',ShortStr,LongStr
from (values ('LOT1','LOT1A'),('LOT1','LOT 1A'),('LOT1','LOT1   '),('LOT1'+char(10),'LOT1B')) WIP(ShortStr,LongStr)
where LongStr like ShortStr+'%'

Select allOpen in new window

If you run the above without the where clause, you would wonder why LOT1B doesnt show....

Thanks, All!

Here is the solution:

rtrim(cast([LOT] as varchar)) LIKE rtrim(cast((select DISTINCT IV30300.ITEMNMBR) as varchar))+'%'

The reason is that select DISTINCT IV30300.ITEMNMBR represents [Component] from WIPTEST.

John