Alternative for len() function in where clause

Question

Alternative for len() function in where clause

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
November 5, 2015 at 10:56 am

#304840

I am trying to find an alternative of len () function in my where clause,i have a simple where condition
AND LEN(SubID) <= 10
AND LEN(SubID) > 3
Any hints please?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you want to check the length of a string and filter on that length, then LEN works fine. Why are you looking for an alternative?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

GilaMonster (11/5/2015)
If you want to check the length of a string and filter on that length, then LEN works fine. Why are you looking for an alternative?

Thank you for prompt response. My theory is if it is using a function on a where clause it will evaluate that function for each satisfying record, now i do i see higher read-aheads (1400) ( don't have any thing to compare), i want how the performance would be without using the len function in the where clause.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yes, a function on a column in the where clause means SQL scans the table, but if you need to filter on the length of a column, there aren't many options. You can either use the function to calculate the length of the column and filter on that, or you can create a computed column using LEN, potentially index it, and filter on that.

But you're still going to be using LEN as it's the easiest way to get the length of a column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Maybe this can prove that LEN() is better than avoiding functions when filtering by length. Even when avoiding functions, you'll get an index scan.

CREATE TABLE #LengthTest(

String varchar(20) PRIMARY KEY); --PK constraint is used as the easiest way to create index

INSERT INTO #LengthTest

VALUES

('1')

,('12')

,('123')

,('1234')

,('12345')

,('123456')

,('1234567')

,('12345678')

,('123456789')

,('1234567890')

,('12345678901')

,('123456789012')

,('1234567890123')

,('12345678901234')

,('123456789012345')

,('1234567890123456');

SELECT *

FROM #LengthTest

WHERE String LIKE REPLICATE('_', 4)

OR String LIKE REPLICATE('_', 5)

OR String LIKE REPLICATE('_', 6)

OR String LIKE REPLICATE('_', 7)

OR String LIKE REPLICATE('_', 8)

OR String LIKE REPLICATE('_', 9)

OR String LIKE REPLICATE('_', 10);

--This uses an implicit conversion which might lead to errors but I could include it in this test

SELECT *

FROM #LengthTest

WHERE String >= CAST( 1000 AS bigint)

AND String < CAST( 10000000000 AS bigint);

GO

DROP TABLE #LengthTest;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101120 More actions · Answer 5

IF that's a highly selective condition -- i.e., there are not many rows where that column has between 4 and 10 bytes -- then you might gain performance by adding a computed column on LEN(SubID) and indexing that computed column.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".