T-SQL PATINDEX -1

Question

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?

Answer 1

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.