Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

T-sql select - like Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 2:11 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:08 PM
Points: 116, Visits: 390
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
Post #1370033
Posted Monday, October 8, 2012 2:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,102, Visits: 3,164
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1370054
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse