May 14, 2014 at 3:56 am
Hi,
can anyone please explain me why i am getting 'no' as answer for below query, instead of 'yes'.
if ('‚')=(',')
begin
print 'yes'
end
else
begin
print 'no'
end
Please refer attached screenshot.
Regards,
Madhu
May 14, 2014 at 3:59 am
If you check using the ASCII function, they're different characters. The first is ASCII code 130, the second is ASCII code 44
SELECT ASCII('‚')
SELECT ASCII(',')
One was copied from Word and one was typed in?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2014 at 4:05 am
Thank you, yes one was from a excel cell value and other typed in editor.
Please also let me know how to change the below query to get desired result.
select * from BordereauRatesSetup where
ClientMaster='MNI' and Voyage_Conveyance='INLAND' and InterestInsured='Machinery‚ Equipment‚ Tools'
The below query is giving the correct result, where as above query returns nothing.
select * from BordereauRatesSetup where
ClientMaster='MNI' and Voyage_Conveyance='INLAND' and InterestInsured='Machinery, Equipment, Tools'
May 14, 2014 at 4:10 am
Um, really?
You have a working query, and you're asking what changes to make? Maybe replace the non-working query with the working query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2014 at 6:03 am
Since you now know that you need to replace the one character, you could try using the REPLACE T-SQL statement on the string (not on the column) to replace the offending character. You can even put this into a stored proc so that it does this on the parameter.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2014 at 6:17 am
madhu.palakurthi (5/14/2014)
Thank you, yes one was from a excel cell value and other typed in editor.Please also let me know how to change the below query to get desired result.
select * from BordereauRatesSetup where
ClientMaster='MNI' and Voyage_Conveyance='INLAND' and InterestInsured='Machinery‚ Equipment‚ Tools'
The below query is giving the correct result, where as above query returns nothing.
select * from BordereauRatesSetup where
ClientMaster='MNI' and Voyage_Conveyance='INLAND' and InterestInsured='Machinery, Equipment, Tools'
The above query has the bad commas as well. Its very useful to get yourself some tools to examine what characters are actually in the text, if it looks like it should work and doesn't. I've run into cases that even notepad inserts garbage (and invisible) characters into what looks like valid text.
I like to use gnu tools for windows and in this case use hexdump, but anything that can show the actual values in your otherwise normal looking text can help you out with troubleshooting.
Heres what happened when I used another gnu tool for windows, "diff", on your two queries:
C:\l>c:\gnu\diff QUERY1.txt QUERY2.txt
2c2
< ClientMaster='MNI' and Voyage_Conveyance='INLAND' and InterestInsured='Machineryâ?s Equipmentâ?s Tools'
---
> ClientMaster='MNI' and Voyage_Conveyance='INLAND' and InterestInsured='Machinery, Equipment, Tools'
I could tell right off the bat you had bad commas in the first query even without using hexdump.
Viewing the two queries with hexdump:
C:\>c:\gnu\hexdump QUERY1.txt
00000000: 73 65 6C 65 63 74 20 2A - 20 66 72 6F 6D 20 42 6F |select * from Bo|
00000010: 72 64 65 72 65 61 75 52 - 61 74 65 73 53 65 74 75 |rdereauRatesSetu|
00000020: 70 20 77 68 65 72 65 0D - 0A 43 6C 69 65 6E 74 4D |p where ClientM|
00000030: 61 73 74 65 72 3D 27 4D - 4E 49 27 20 61 6E 64 20 |aster='MNI' and |
00000040: 56 6F 79 61 67 65 5F 43 - 6F 6E 76 65 79 61 6E 63 |Voyage_Conveyanc|
00000050: 65 3D 27 49 4E 4C 41 4E - 44 27 20 61 6E 64 20 49 |e='INLAND' and I|
00000060: 6E 74 65 72 65 73 74 49 - 6E 73 75 72 65 64 3D 27 |nterestInsured='|
00000070: 4D 61 63 68 69 6E 65 72 - 79 E2 80 9A 20 45 71 75 |Machinery Equ|
00000080: 69 70 6D 65 6E 74 E2 80 - 9A 20 54 6F 6F 6C 73 27 |ipment Tools'|
00000090: 0D 0A - | |
00000092;
C:\>c:\gnu\hexdump QUERY2.txt
00000000: 73 65 6C 65 63 74 20 2A - 20 66 72 6F 6D 20 42 6F |select * from Bo|
00000010: 72 64 65 72 65 61 75 52 - 61 74 65 73 53 65 74 75 |rdereauRatesSetu|
00000020: 70 20 77 68 65 72 65 0D - 0A 43 6C 69 65 6E 74 4D |p where ClientM|
00000030: 61 73 74 65 72 3D 27 4D - 4E 49 27 20 61 6E 64 20 |aster='MNI' and |
00000040: 56 6F 79 61 67 65 5F 43 - 6F 6E 76 65 79 61 6E 63 |Voyage_Conveyanc|
00000050: 65 3D 27 49 4E 4C 41 4E - 44 27 20 61 6E 64 20 49 |e='INLAND' and I|
00000060: 6E 74 65 72 65 73 74 49 - 6E 73 75 72 65 64 3D 27 |nterestInsured='|
00000070: 4D 61 63 68 69 6E 65 72 - 79 2C 20 45 71 75 69 70 |Machinery, Equip|
00000080: 6D 65 6E 74 2C 20 54 6F - 6F 6C 73 27 20 0D 0A |ment, Tools' |
0000008f;
May 14, 2014 at 6:18 am
Grant Fritchey (5/14/2014)
Since you now know that you need to replace the one character, you could try using the REPLACE T-SQL statement on the string (not on the column) to replace the offending character. You can even put this into a stored proc so that it does this on the parameter.
+1
Although it does make you wonder if it might be more than just the one instance you are seeing.
May 14, 2014 at 6:26 am
Greg Edwards-268690 (5/14/2014)
Grant Fritchey (5/14/2014)
Since you now know that you need to replace the one character, you could try using the REPLACE T-SQL statement on the string (not on the column) to replace the offending character. You can even put this into a stored proc so that it does this on the parameter.+1
Although it does make you wonder if it might be more than just the one instance you are seeing.
All the more reason to get the queries into stored procedures so you have the ability to clean the data, both inputs and search criteria. If you can't guarantee a consistent input from the client side, then you have to guarantee it from the data side. One of the reasons for stored procs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2014 at 7:52 am
Grant Fritchey (5/14/2014)
Greg Edwards-268690 (5/14/2014)
Grant Fritchey (5/14/2014)
Since you now know that you need to replace the one character, you could try using the REPLACE T-SQL statement on the string (not on the column) to replace the offending character. You can even put this into a stored proc so that it does this on the parameter.+1
Although it does make you wonder if it might be more than just the one instance you are seeing.
All the more reason to get the queries into stored procedures so you have the ability to clean the data, both inputs and search criteria. If you can't guarantee a consistent input from the client side, then you have to guarantee it from the data side. One of the reasons for stored procs.
DW background, so we always fixed wherever we could on the input into the ERP system.
Ran into an ugly one once when working with a China instance.
First, they imported Traditional Chinese into db that was supposed to be Simplified Chinese.
And then sometimes the double bit was messed up.
Interesting issues to trace down and get fixed.
Especially when it was the first time using Unicode, having no clue how to read Chinese, and then dealing with windows regional and language settings.
Eventually got the ERP programmers to fix things, but it took awhile.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply