T-sql select

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » T-sql select - like

12 posts, Page 2 of 2

««1 2

T-sql select - like

Rate Topic

Display Mode

Topic Options

Author

Message

jarid.lawson

Posted Monday, October 08, 2012 2:11 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 01, 2013 12:29 PM
Points: 99, Visits: 327

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

ScottPletcher

Posted Monday, October 08, 2012 2:47 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1370054

« Prev Topic | Next Topic »

12 posts, Page 2 of 2

««1 2

Permissions