Not able to retrieve the records checking the exact data stored in Description field
-
Hi
I am trying to retrieve the records using the following query, the "Descrip" column data type is set to "Varchar", I do not see any special characters also in the text that is put in the condition. Can someone please help me to understand why it is failing to retrieve the records.
select * from SaTrans
where
Descrip='DTEF IN-10139 PAID ON 22.04.2015. AMOUNT PAID IS P52,541,500, CN-10013 MISMATCHED TO IN-10140 BUT '
-
You'll need to provide more of a description of what is and isn't working. We can't see your data, so please include the table DDL, some sample data, and then what should be returned.
If you are saying that this exact record isn't being returned, are you use you don't have any missing spaces (or extras)?
-
Please see the image below i am trying to find out the counts using the exact data stored in the Descrip column when i used the exact data stored in that column it gives Zero records, but when i use like operator it gives the correct count. Why is it like that?, the "Descrip" column datatype is Varchar, as per my knowledge it allows the special characters also, correct?. When i used the exact data which is stored in that column why is it giving Zero?
I hope I gave the clarity now, please help me
you can also see the queries and outputs as follows
select COUNT(1) from SaTrans
where Descrip='DTEF IN-10139 PAID ON 22.04.2015. AMOUNT PAID IS P52,541,500, CN-10013 MISMATCHED TO IN-10140 BUT '
--Query output:
--(No column name)
--0
select COUNT(1) from SaTrans where Descrip like 'DTEF IN-10139 PAID ON 22.04.2015.%'
--Query output:
--(No column name)
--2704
-
I cannot see the picture you included, but I can give you a generic reply.
Problems like this are often caused by invisible characters in the data. In a system on my workplace, data is often "padded" with carriage return/linefeed because users enter that data by using copy/paste from a text file and fail to notice that the selected string includes the end of the line.
Sometimes you can see this by running the query with LIKE, selecting a cell with the output, then using copy/paste into a text editor. Or even a separate query window.
Sometimes you can see this by running the query with LIKE with "result to text" instead of "result to grid" mode.
You can always find this by running the query with LIKE plus a TOP to select just 1 row, put the result in a variable, then code a loop that shows the Ascii value of each character in the loop. I put this suggestion last is it's a royal pain to have to do so.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hi Hugo
Thanks for the suggestions, in fact, I have tried with the first and second suggestion what you have mentioned, but not tried the third one. will check now. I am more interested to know what could be there reason, though I managed to get the things done when I use like operator.
I am also attaching the picture I am taking about, If you can't see the attachment please see the below query and results
you can also see the queries and outputs as follows
select COUNT(1) from SaTrans
where Descrip='DTEF IN-10139 PAID ON 22.04.2015. AMOUNT PAID IS P52,541,500, CN-10013 MISMATCHED TO IN-10140 BUT '
--Query Results:
--(No column name)
--0
select COUNT(1) from SaTrans where Descrip like 'DTEF IN-10139 PAID ON 22.04.2015.%'
--Query Results:
--(No column name)
--2704
-
In the query with LIKE, replace "COUNT(1)" with "Descrip". Check if the rest of the text matches as well. Your like only checks the first part, there might be differences in the rest of the description.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
I have used the like operator in the second query to show the difference between, when I use exact match the result is Zero but when i use like operator the result is one as it is bringing the intended output. My interest is to know why it is not bringing any records or counting any records when i use the exact match.
-
Yes, I understand why you used the LIKE operator. I also see from the query results that your table contains 2704 rows that have a description that starts with "DTEF IN-10139 PAID ON 22.04.2015."
The reason I suggest replacing the COUNT with the actual column is so that you can then inspect the rest of the description in those columns. For all I know, all 2704 of them might have a description equal to "DTEF IN-10139 PAID ON 22.04.2015. Nothing more here. Fooled 'ya!" If that is the case, then getting no rows back on the equality test makes sense because none of them is equal to the string you are searching for: "DTEF IN-10139 PAID ON 22.04.2015. AMOUNT PAID IS P52,541,500, CN-10013 MISMATCHED TO IN-10140 BUT"
By looking at the actual text of the description in the rows that match the LIKE, you might perhaps see why there are no matches. Perhaps the row(s) you are looking for have an even longer description? (The word "but" does suggest that there is more text to follow...)
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Thank you so much for the details explanation, I got the problem where it is.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply