CODEWITHSUNDEEP
sqlsql-server
Andy
Andy

Reputation: 1090

PATINDEX vs LEN performance MS SQL

Which of these two functions will have better performance to find the length of the input string?

PATINDEX ('%%{terminator}%%',inputString)

or

LEN(inputString)

Upvotes: 1

Views: 943

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416081

The length of a string in sql server is often stored separately from the text. You don't have to walk the string to know how long it is. It's just a lookup, or a lookup followed by walking from the end of the string (to check trailing spaces), rather than the beginning. Assuming you were able to construct a PATINDEX to return the last index of the string, that would still require you to walk the string to evaluate the length. I suppose you could construct a scenario where PATINDEX was faster, but only if you have strings that consist mostly of trailing space.

So LEN() wins here in any way you can think of: semantics (best communicates meaning of what you're doing), correctness (getting the PATINDEX right would be tricky), and performance.

Upvotes: 4

Related Questions