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 |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-11-18 : 23:07:49
|
| I've spent hours debugging a problem, got it down to a search condition that was behaving oddly.I have a proc that deletes users by tagging them deleted, and modifying the username thus:'[deleted]'+usernameThe search conditionWHERE username like '%[deleted]'was the bug. I need to eliminate the brackets [] because it means "match any character between the brackets" for the LIKE operator.The quick fix is to eliminate the brackets. I was wondering if there was a syntax to keep the brackets??Sam |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-18 : 23:25:41
|
| Look at the escape clause on the like statememt.Why do you want to change the username if you already have a flag?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-11-18 : 23:26:47
|
Its not sargable but:WHERE left(data,9) = '[deleted]' |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-11-18 : 23:45:37
|
| First of all, thank you guys, and what are you guys doing up at this hour?I hadn't heard of LIKE's Escapematch_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] Thanks. And good question. Why add [delete]? It's a way of retaining the record for historic purposes, yet allowing the user to be added again in the future. A rare occurance, but it has happened. In this instance, we don't want the new record to have the old history so we do not reinstate the prior record.Sam |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-18 : 23:48:39
|
| Oh - I thought you had a deleted flag as well.It would be better to use a flag than to change the name.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-11-18 : 23:54:06
|
| I do have a deleted flag which is why I ran in circles explaining my twisted logic about why the Username field is prefaced with [deleted].There's a UNIQUE constraint on the Username field. Modifying the deleted username allows reinsertion of the same user at a later date without violating the constraint.Not a pretty picture.Sam |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-11-19 : 00:04:33
|
| How would you work up the pattern / escape character to catch '[deleted]' ? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-19 : 00:19:09
|
| like '|[deleted|]%' escape '|'==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Hair pulling wild ride