CODEWITHSUNDEEP
sqlsql-servert-sqlpatindexcharindex
Alessio Bacin
Alessio Bacin

Reputation: 69

T-SQL PATINDEX -1

this is my first post here and i'm also a newby in development. In any case.. my problem is:

sql statement:

SELECT left(clienti.SedeLegaleIndirizzo, patindex('%[0-9]%',clienti.SedeLegaleIndirizzo))
AS indirizzo from Clienti

clienti.SedeLegaleIndirizzo is clienti table and SedeLegaleIndirizzois the column with the address including streen and number. I want to separate street from number but with my statement i get the street with the first number. As i know from charindex i can add -1 to the last parameter but the problem is that it returns me this error if i put that parameter with patindex:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

I'm using patindex and not charindex cause i'm searching a number...

What is anybody's suggestion?

Upvotes: 1

Views: 1458

Answers (2)

jpw
jpw

Reputation: 44911

The reason you're getting the error is most likely due to rows without numbers which would make the result of patindex(...) -1 negative.

One solution is to exclude those rows:

SELECT LEFT(clienti.SedeLegaleIndirizzo, PATINDEX('%[0-9]%',clienti.SedeLegaleIndirizzo) - 1) 
AS indirizzo FROM Clienti
WHERE PATINDEX('%[0-9]%',clienti.SedeLegaleIndirizzo) > 0

Upvotes: 0

asontu
asontu

Reputation: 4659

It's not entirely clear to me where you are placing the -1 but it looks you are doing this:

SELECT left(clienti.SedeLegaleIndirizzo,
        patindex('%[0-9]%', clienti.SedeLegaleIndirizzo)-1)

This will give an issue when the number is not found, because then patindex() gives 0 and 0-1=-1. You can't have a left() with length -1. My solution in these cases is this:

SELECT left(SedeLegaleIndirizzo,
        isnull(nullif(patindex('%[0-9]%', SedeLegaleIndirizzo)-1, -1), 0))

This ensures that if the number is not found, in stead of trying to do left(SedeLegaleIndirizzo, -1) the code will result in left(SedeLegaleIndirizzo, 0) and simply give an empty string.

Upvotes: 1

Related Questions