question

dvprao avatar image
dvprao asked

Searching for non-alphanumeric values in a column

This is my query: SELECT shipment FROM dbo.tablename WHERE fieldname LIKE '%[^a-zA-Z0-9./_=()+*& -]%' I get these results: I don't understand why some values like the first one are returned. Also I am getting different results when I move the hypen ( minus) character (-) to either side in the expression. ------------------------ PAC 441-2/20/2013 SG MSCDG190212-1 SFODAL12FEB13 MSCDG190212-1 DR1207-04 BAH-1102-04 b SFODAL26APR2013 SFOUAL17JUN13 PAC-MAN!$)^!£ PAC-MAN!$)^!£ PAC-MAN!$)^!£ SFOUAL17JUN13 BAH-1102-04 b BAH-1102-04 b
sql-server-2005query-resultssearch
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image Mister Magoo commented ·
Are you sure that you don't have some non-printable characters in there? Also, what data type is the "fieldname" column?
0 Likes 0 ·
Mister Magoo avatar image
Mister Magoo answered
You have some TABs in there (HEX 09), which is why they are being selected. try removing them.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvprao avatar image
dvprao answered
Field is varchar(30) It appears that there are some trailing spaces -- But then the filter is supposed to allow it, isn't it ? ( I thought the query should only result in 3 rows with PAC-MAN!$)^!£. More over when I move the position of the hyphen character in the query either left or right , the results vary...for example if I move the hyphen ("-") to say a position immediately after 9 , the filter does not seem to work. I am getting better results only if the hyphen is the last character. ------------ SELECT shipment , len(shipment) string_length FROM dbo.onhand WHERE shipment LIKE '%[^a-zA-Z0-9./_=()+*& -]%' -------------------- shipment string_length PAC 441-2/20/2013 SG 21 MSCDG190212-1 15 SFODAL12FEB13 14 MSCDG190212-1 15 DR1207-04 10 BAH-1102-04 b 13 SFODAL26APR2013 16 SFOUAL17JUN13 15 PAC-MAN!$)^!£ 13 PAC-MAN!$)^!£ 13 PAC-MAN!$)^!£ 13 SFOUAL17JUN13 15 BAH-1102-04 b 13 BAH-1102-04 b 13
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image Mister Magoo commented ·
have you tried escaping the hypen? It does have special meaning, so should be - although I think having it last may negate the need for escaping it.
0 Likes 0 ·
dvprao avatar image
dvprao answered
Yes , I did , does not help. I am totally confused now :-)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image Mister Magoo commented ·
Did you check for non-printable? output the shipment column converted to varbinary
1 Like 1 ·
dvprao avatar image
dvprao answered
shipment string_length (No column name) MSCDG190212-1 15 0x4D534344473139303231322D310909 PAC 441-2/20/2013 SG 21 0x504143203434312D322F32302F3230313320534709 MSCDG190212-1 15 0x4D534344473139303231322D310909 SFODAL12FEB13 14 0x53464F44414C3132464542313309 DR1207-04 10 0x4452313230372D303409 SFODAL26APR2013 16 0x53464F44414C32364150523230313309 BAH-1102-04 b 13 0x4241482D313130322D30340962 SFOUAL17JUN13 15 0x53464F55414C31374A554E31330909 PAC-MAN!$)^!£ 13 0x5041432D4D414E2124295E21A3 PAC-MAN!$)^!£ 13 0x5041432D4D414E2124295E21A3 PAC-MAN!$)^!£ 13 0x5041432D4D414E2124295E21A3 SFOUAL17JUN13 15 0x53464F55414C31374A554E31330909 BAH-1102-04 b 13 0x4241482D313130322D30340962 BAH-1102-04 b 13 0x4241482D313130322D30340962
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvprao avatar image
dvprao answered
sorry formatting got messed up . ------------ shipment string_length (No column name) MSCDG190212-1 15 0x4D534344473139303231322D310909 PAC 441-2/20/2013 SG 21 0x504143203434312D322F32302F3230313320534709 MSCDG190212-1 15 0x4D534344473139303231322D310909 SFODAL12FEB13 14 0x53464F44414C3132464542313309 DR1207-04 10 0x4452313230372D303409 SFODAL26APR2013 16 0x53464F44414C32364150523230313309 BAH-1102-04 b 13 0x4241482D313130322D30340962 SFOUAL17JUN13 15 0x53464F55414C31374A554E31330909 PAC-MAN!$)^!£ 13 0x5041432D4D414E2124295E21A3 PAC-MAN!$)^!£ 13 0x5041432D4D414E2124295E21A3 PAC-MAN!$)^!£ 13 0x5041432D4D414E2124295E21A3 SFOUAL17JUN13 15 0x53464F55414C31374A554E31330909 BAH-1102-04 b 13 0x4241482D313130322D30340962 BAH-1102-04 b 13 0x4241482D313130322D30340962
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvprao avatar image
dvprao answered
Got it : Thank you very much. (of course it is another matter as to why would anyone allow a field to contain multiple tab characters as valid - apparently ORACLE does) Can you please throw some light on the hyphen's special meaning ? ( apart from indicating a range ? --------------------------------- SELECT shipment , len(shipment) string_length , convert (varbinary , shipment ) FROM dbo.onhand WHERE shipment LIKE '%[^a-zA-Z0-9./_=()+*& -]%' ---------------------- shipment string_length (No column name) PAC-MAN!$)^!£ 13 0x5041432D4D414E2124295E21A3 PAC-MAN!$)^!£ 13 0x5041432D4D414E2124295E21A3 PAC-MAN!$)^!£ 13 0x5041432D4D414E2124295E21A3
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image Mister Magoo commented ·
Just that meaning, indicating a range, nothing else. Glad we found it - this sort of unexpected behavior is often down to non-printables...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.