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
Are you sure that you don't have some non-printable characters in there? Also, what data type is the "fieldname" column?
You have some TABs in there (HEX 09), which is why they are being selected. try removing them.
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
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.
Yes , I did , does not help. I am totally confused now :-)
Did you check for non-printable? output the shipment column converted to varbinary
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
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
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
Just that meaning, indicating a range, nothing else. Glad we found it - this sort of unexpected behavior is often down to non-printables...
14 People are following this question.
