Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dlclark
Starting Member
1 Post |
 Posted - 2002-11-18 : 18:41:41
|
| Hi all.I'm trying to trap bad data that doesn't fit the pattern for the uniqueidentifier field. In the data I'm importing, it is in character format, so the import will fail unless the text fits the following pattern:xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxx, where each character can only be A-F or 0-9I'm trying to write an SQL statement to select out those NOT fitting that pattern to protect my import process from failing and to flag those that need further review.I've combined checks for all characters not A-F (LIKE '%[G-Z}%') and the overall length of the string (LEN(string) <> 36), but that still doesn't work if a '-' is out of place.Any ideas?Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-18 : 18:50:11
|
| Change the x's in the pattern to underscores:________-____-____-____________And use it in a LIKE:...AND NOT LIKE '________-____-____-____________' |
 |
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2002-11-19 : 00:02:26
|
| But that would allow characters like G-Z You want something like AND NOT LIKE 'xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxx'where each x is replaced by [A-F0-9]so it becomesAND NOT LIKE '[A-F0-9][A-F0-9][A-F0-9] etc etc .... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-19 : 08:22:58
|
I meant to say use the underscores in addition to the other LIKE patterns already being used.  There would be at least two LIKE patterns used for matching. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-11-19 : 11:45:43
|
| I don't think that works if there are spurious hyphens: hyphens are not in the range [G-Z] but are matched by _.You can, of course, do the replacement at runtime, which makes things a bit clearer... well, shorter anyway:NOT LIKE REPLACE('xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxx', 'x', '[0-9A-F]') |
|
|
|
|
|
|
Pattern matching problems