SUBSTRING Question

Question

SUBSTRING Question

ehlinger

Hall of Fame

Points: 3585
More actions
March 14, 2007 at 11:15 am

#116637

I have a table that contains a county and state separated by a comma. I am trying to break the county and state into their own fields. I have the following query that is close to doing what I want but when I add in the -1 in the first SUBSTRING I get an invalid length parameter passed to the substring function. It is a bit puzzling because I am doing the same thing later in the query to get the state, only adding character spaces onto the CHARINDEX result instead of taking them off.   What am I missing? Here is the query:
SELECT    SUBSTRING(OrigCounty, 1, CHARINDEX(',', origCounty)-1) AS [COUNTY],
                CHARINDEX(',', origCounty) AS [Check to see what CHARINDEX is returning],
                CHARINDEX(',', origCounty)-1 AS [Check to see what CHARINDEX -1 is returning],
                SUBSTRING(OrigCounty, CHARINDEX(',', origCounty)+2, LEN(OrigCounty)) AS [STATE],
                OrigCounty AS [Oringinal String]
FROM                tmpCounties

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

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

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 1

In order for this to work, all the OrigCounty data must have the data exactly as you suspect... (County, State). If you have a null or a blank or maybe even just a county and no state, I don't think this will work. You probably have one piece of data that doesn't have a comma. CHARINDEX will return a 0 in this case, and you will be using -1 as a length parameter.

mrpolecat SSCertifiable Points: 6964 More actions · Answer 2

most likely one of your rows does not have a comma in it. Try adding the where clause "where origcounty like '%,%'" and see if it works

Cory E. SSCertifiable Points: 7076 More actions · Answer 3

Yeah - I was just about to reply that I had no issues with the SQL you had, then I saw MePoleCat's response, and confirmed the error:

create

table #tmpCounties

(

OrigCounty varchar(50))

insert

into #tmpCounties values ('Anoka County, MN')

insert into #tmpCounties values (' MN')

SELECT SUBSTRING(OrigCounty, 1, CHARINDEX(',', origCounty)-1) AS [COUNTY],

CHARINDEX(',', origCounty) AS [Check to see what CHARINDEX is returning],

CHARINDEX(',', origCounty)-1 AS [Check to see what CHARINDEX -1 is returning],

SUBSTRING(OrigCounty, CHARINDEX(',', origCounty)+2, LEN(OrigCounty)) AS [STATE],

OrigCounty

AS [Oringinal String]

FROM

#tmpCounties

Results:

(1 row(s) affected)

COUNTY Check to see what CHARINDEX is returning Check to see what CHARINDEX -1 is returning STATE Oringinal String

----------------- --- --- --- ---------------------

Anoka County 13 12 MN Anoka County, MN

Msg 536, Level 16, State 5, Line 12

Invalid length parameter passed to the SUBSTRING function.

-- Cory

ehlinger Hall of Fame Points: 3585 More actions · Answer 4

Thanks everyone. That was exactly the problem. There were some records that just had the name of the state and obviously no comma.

mrpolecat SSCertifiable Points: 6964 More actions · Answer 5

another issue you may have with this is whether the space is there after the comma for the state. It would be better to take everything after the comma and trim it rather that assuming there will be a space after the comma. I ran into this with my test data because I did not iclude a space and only got back one character of the state.

SELECT ltrim(rtrim(SUBSTRING(OrigCounty, 1, CHARINDEX(',', origCounty)-1))) AS [COUNTY],

CHARINDEX(',', origCounty) AS [Check to see what CHARINDEX is returning],

CHARINDEX(',', origCounty)-1 AS [Check to see what CHARINDEX -1 is returning],

ltrim(rtrim(SUBSTRING(OrigCounty, CHARINDEX(',', origCounty)+1, LEN(OrigCounty)))) AS [STATE],

OrigCounty AS [Oringinal String]

FROM tmpCounties