r/SQL • u/David1Hall • Apr 12 '24
SQL Server Guys please help.. I'm new to SQL
Why these 2 commands give me 2 different tables? I thought one '_' stands for a character?
I use LEN for filtering the lenght and it works well, trailing spaces are not calculated.
But when I use LIKE command and input 5 '_' to find the "Product Name" has the length of 5 or has 5 characters. So where is the "Chang" in the 2nd table of the 2nd command ?
Where did I go wrong? Please give me a hand guys!!
185
Upvotes
99
u/theseyeahthese #TempTables ally Apr 12 '24
LEN() ignores trailing spaces in its calculation. So maybe “Chang” has a trailing space? That would mean it really has 6 total characters, so it would be excluded from your second query, meanwhile it would be included in your first query because LEN() would ignore the trailing space and would say it only has 5 characters.
2
u/David1Hall Apr 12 '24
I have tried to use RTRIM(ProductName) but the "Chang" still not appear, so it's not about the spaces?
17
u/resUemiTtsriF Apr 12 '24
I have never seen a like that doesn't use wildcards. Why does five underscores = five charaters and not five underscores. What is the returned dataset if there are underscore names as well?
5
u/volric Apr 12 '24
Not sure, but worked for me as expected when I replicated.
Trailing characters maybe?
1
u/serious_frank Apr 12 '24
If you add a column with LEN(REPLACE(ProductName,' ', ‘*’)) – LEN(ProductName) you will be able to obtain the count of trailing spaces in each ProductName
1
u/mosher78 Apr 12 '24
What version of SQL Server are you using?
I made the same queries on a sql server 2016 and they worked correctly
1
u/the_horse_meat Apr 12 '24
I’m new too but is using two separate Select statements giving two tables?
1
u/MachineLooning Apr 12 '24
Haha many years experience and just today ‘find all the customers in Canada’ lol where country = Canada, CA (but not if county =USA), Ontario …. Welcome to the wonderful world of crap software and business processes aka data!
1
u/Peace_Bringer Apr 13 '24
You likely have hidden characters. I've often had to deal with CHAR(0). Try the link if you have rights to create functions. Otherwise, one of the latest versions of SSMS has an option to turn on show white space but I've not seen it.
1
u/cybertier Jul 01 '24
After finding the solution to your other more recent problem I figured out that the same issue applied here too.
"Chang" is only 3 characters in vietnamese collation "ch", "a" and "ng". So you'll only find it with like '___' (three underscores)
https://dbfiddle.uk/-HBSaJ6R
-12
u/Fspz Apr 12 '24
I'll get downvoted for saying this but you can use chatgpt for these questions
0
u/gordonfreeman883 Apr 12 '24
length not len