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?

What you want to do? – Pranav Bilurkar Jan 26 '15 at 09:24 — Pranav Bilurkar, Jan 26 '15 at 09:24

score 1 · Accepted Answer · answered Jan 26 '15 at 09:39

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.

Happy to help. If my answer worked for you, could you click to accept it? It helps me and the website :) thanks! — asontu, Jan 27 '15 at 08:06

score 0 · Answer 2 · answered Jan 26 '15 at 09:40

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

T-SQL PATINDEX -1

2 Answers2