SQL _ wildcard not working as expected. Why?
sql, sql-server, sql-server-2012, t-sql
Solution
The case could be that the field is a large empty string? Such as `"15123 "`
You could also try another solution?
select id, col1, len(col1)
from tableA
where col1 like '15%' AND Len(col1)=5
EDIT - FOR FUTURE REFERENCE:
For sake of comprehensiveness, char and nchar uses the full field size, so char(10) would be `15________` ("15" + 8 characters) long, because it implicitly forces the size, whereas a varchar resizes based on what it is supplied `15` is simply `15`.
To get around this you could
A) Do an LTRIM/RTRIM To cut off all extra spaces
select id, col1, len(col1)
from tableA
where rtrim(ltrim(col1)) like '15___'
B) Do a LEFT() to only grab the left 5 characters
select id, col1, len(col1)
from tableA
where left(col1,5) like '15___'
C) Cast as a varchar, a rather sloppy approach
select id, col1, len(col1)
from tableA
where CAST(col1 AS Varchar(192)) like '15___'
Problem
so i have this query ``` select id, col1, len(col1) from tableA ``` from there I wanted to grab all data in col1 that have exactly 5 characters and start with 15 ``` select id, col1, len(col1) from tableA where col1 like '15___' -- underscore 3 times ``` Now col1 is a nvarchar(192) and there are data that starts with 15 and are of length 5. But the second query always shows me no rows. Why is that?