SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-sql select - like


T-sql select - like

Author
Message
jarid.lawson
jarid.lawson
Mr or Mrs. 500
Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)

Group: General Forum Members
Points: 583 Visits: 436
ScottPletcher (10/8/2012)


SELECT lastname
FROM HR.Employees
WHERE
lastname LIKE '%[/]%' AND
lastname NOT LIKE '%[/]%[/]%'



Still have to add the line that removes / in the first character. I also believe you would want to remove any where / was the last character...
And Left(Ltrim(LastName), 1) <> '/'
And Right(Rtrim(LastName),1) <> '/'



I'm curious if this would not be a good option from an execution plan perspective? Are there better / faster ways of completing this?

“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson

ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19301 Visits: 7410
jarid.lawson (10/8/2012)
ScottPletcher (10/8/2012)


SELECT lastname
FROM HR.Employees
WHERE
lastname LIKE '%[/]%' AND
lastname NOT LIKE '%[/]%[/]%'



Still have to add the line that removes / in the first character. I also believe you would want to remove any where / was the last character...
And Left(Ltrim(LastName), 1) <> '/'
And Right(Rtrim(LastName),1) <> '/'



I'm curious if this would not be a good option from an execution plan perspective? Are there better / faster ways of completing this?



Oops, I didn't initially read down far enough to see the new requirement of not a leading /:


SELECT lastname
FROM HR.Employees
WHERE
lastname LIKE '[^/]%[/]%' AND
lastname NOT LIKE '[^/]%[/]%[/]%'

[/quote]


In theory this could potentially be faster with a covering index on lastname (probably likely, if you plan on running this query very often), whereas the queries using REPLACE(...) or any other function on the lastname column would require a full table scan.

That is, "lastname [NOT] like" is SARGable but "<function>(... lastname ...)" is not.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search