Solved

SQL Syntax

Posted on 2009-04-03
16
272 Views
Last Modified: 2012-05-06
Hi. I have a field in a table called CityAndState.  It contains one of the following for data

CityName <space> StateName
 
or

CityName <comma> StateName

Now, I have code that will parse it during the select and break it up into separate values for City and State which looks like the code blelow.

HOWEVER, it doesn't work with city names that have more than one space. For example, Sacramento CA it works fine with. San Francisco CA it does not.

Any suggestions on how to look for a SECOND occurence of the space?

Thanks in advance


CASE WHEN 

	CHARINDEX(',', ri.CityAndState) > 0 THEN RTRIM(LTRIM(LEFT(ri.CityAndState, CHARINDEX(',', ri.CityAndState + ' ') - 1)))

     ELSE

	CASE WHEN

		CHARINDEX(' ', ri.CityAndState) > 0 THEN RTRIM(LTRIM(LEFT(ri.CityAndState, CHARINDEX(' ', ri.CityAndState + ' ') - 1)))

	     ELSE

		ri.CityAndState

	END

END AS CITY,

CASE WHEN 

	CHARINDEX(' ', ri.CityAndState) > 0 THEN RTRIM(LTRIM(SUBSTRING(ri.CityAndState, CHARINDEX(' ',ri.CityAndState) + 1, LEN(ri.CityAndState)))) 

     ELSE 

	NULL 

END AS STATE

Open in new window

0
Comment
Question by:WaldaInc
  • 10
  • 5
16 Comments
 
LVL 11

Expert Comment

by:Swindle
ID: 24062257
Try using the REPLACE function to get rid of the commas so you are always looking for a space.  That's the first step.  Then use the REVERSE function to flip the string around and find the last space which you know will always be the  one between the city and the state.  Here's a test to show you want I'm talking about along with what I think  you would need to put in your query to get the result you want.
--Example to test with.  Just plug in different values to @CityAndState to test results

DECLARE @CityAndState as varchar(100)

SET @CityAndState = 'San Francisco, CA'
 

SELECT LEFT(REPLACE(@CityAndState, ', ',' '), len(REPLACE(@CityAndState, ', ',' ')) - CHARINDEX(' ', REVERSE(REPLACE(@CityAndState, ', ',' '))) + 1) as City,

	RIGHT(REPLACE(@CityAndState, ', ',' '),CHARINDEX(' ', REVERSE(REPLACE(@CityAndState, ', ',' '))) - 1) as State
 

--This is what I think you actually need in your statement

SELECT LEFT(REPLACE(ri.CityAndState, ', ',' '), len(REPLACE(ri.CityAndState, ', ',' ')) - CHARINDEX(' ', REVERSE(REPLACE(ri.CityAndState, ', ',' '))) + 1) as City,

	RIGHT(REPLACE(ri.CityAndState, ', ',' '),CHARINDEX(' ', REVERSE(REPLACE(ri.CityAndState, ', ',' '))) - 1) as State

Open in new window

0
 

Author Comment

by:WaldaInc
ID: 24062369
Sorry, but those did not work at all. I'm getting everything returined basically as the CityAndState column, it does not split out the values at all. It does however, remove the comma.

The code I had did work for getting comma seperated or space seperated CityAndState, it just didn't work for the extra space city names.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24062818
Thy this way out...

select 
 

cityandstate

,case when charindex(',',cityandstate)>0 

     then left(cityandstate, len(cityandstate)-charindex(',',reverse(cityandstate)))

     else left(cityandstate, len(cityandstate)-charindex(' ',reverse(cityandstate)))

end

,case when charindex(',',cityandstate)>0 

     then ltrim(right(cityandstate, charindex(',',reverse(cityandstate))-1))

     else ltrim(right(cityandstate, charindex(' ',reverse(cityandstate))-1))

end
 

from (select 'san francisco, ca' as CityAndState

union all select 'new york NY'

union all select 'york, pa'

union all select 'sacramento ca') ri

Open in new window

0
 

Author Comment

by:WaldaInc
ID: 24063007
Thjanks Brandon.

Your code is nice and it works within the context of your post. However, when I put the code into my SQL Statement I get the following error:

Invalid length parameter passed to the LEFT or SUBSTRING function.


This is coming from the len(cityandstate) portion as when I replace that portion with a hard-coded value it works.

Now is my code I am doing the len on the actual column name from the database. Not sure if that is what is casuing the issue.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24063177
If you have data that does not have a space or a , then that will happen.  I mistakenly assumed it always would.

This should fix it.
select 

 

cityandstate

,case when charindex(',',cityandstate)>0 then left(cityandstate, len(cityandstate)-charindex(',',reverse(cityandstate)))

when charindex(' ',cityandstate)>0 then left(cityandstate, len(cityandstate)-charindex(' ',reverse(cityandstate)))

     else cityandstate

end

,case when charindex(',',cityandstate)>0  then ltrim(right(cityandstate, charindex(',',reverse(cityandstate))-1))

when charindex(',',cityandstate)>0  then ltrim(right(cityandstate, charindex(' ',reverse(cityandstate))-1))

     else null

end

 

from (select 'san francisco, ca' as CityAndState

union all select 'new york NY'

union all select 'york, pa'

union all select 'sacramento ca'

union all select 'pittsburgh') ri

Open in new window

0
 

Author Comment

by:WaldaInc
ID: 24063498
Nope same error as before. Also, your code run as it is, doesn't return a 'STATE' value for New York NY. It comes out as NULL.

It seems that SQL does not line the second parameter for Left to be a calcualation for example Len(something) - somethingelse.  It chokes on that. Once I remove the - (minus) portion, it runs. Weird, I know VB and C# support that and you would assume that SQL does too, but apparently not.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24063543
Ok.  I forgot to change something in the copy/paste.  Can you tell me what data is causing it that I'm not accounting for.  My FROM below has sample data with 2 part cities, 1 part cities, and with/without commas as well as single city name with not state.
select 

 

cityandstate

,case when charindex(',',cityandstate)>0 then left(cityandstate, len(cityandstate)-charindex(',',reverse(cityandstate)))

when charindex(' ',cityandstate)>0 then left(cityandstate, len(cityandstate)-charindex(' ',reverse(cityandstate)))

     else cityandstate

end

,case when charindex(',',cityandstate)>0  then ltrim(right(cityandstate, charindex(',',reverse(cityandstate))-1))

when charindex(' ',cityandstate)>0  then ltrim(right(cityandstate, charindex(' ',reverse(cityandstate))-1))

     else null

end

 

from (select 'san francisco, ca' as CityAndState

union all select 'new york NY'

union all select 'york, pa'

union all select 'sacramento ca'

union all select 'pittsburgh') ri

Open in new window

0
 

Author Comment

by:WaldaInc
ID: 24064028
I'm confused.

Your code (the latest) works just fine.  However when I use your code intermixed with mine, I get the error.

It's on this line, because if I remove this line (replace it with hard-coded value) it runs.

CASE WHEN CHARINDEX(',', ri.CityAndState) > 0
THEN left(ri.CityAndState, len(ri.CityAndState)-charindex(',',reverse(ri.CityAndState)))

Below is the entire SQL statement, but it is the THEN branch that is erroring out. Error message is:

Invalid length parameter passed to the LEFT or SUBSTRING function.
SELECT LastName, ISNULL(RTRIM(LTRIM(LEFT(ri.FirstandMiddle, CHARINDEX(' ', ri.FirstandMiddle + ' ') - 1))), '') AS FirstName, 

ISNULL(CASE WHEN CHARINDEX(' ', ri.FirstandMiddle) = 0 THEN NULL ELSE RTRIM(LTRIM(SUBSTRING(ri.FirstandMiddle, CHARINDEX(' ',ri.FirstandMiddle) + 1, LEN(ri.FirstandMiddle)))) END, '') AS MiddleName, ri.SSN, ri.AltID AS AltID, ri.PersonID, 

ri.SalaryTotal, ri.SerialNumber, ri.BasedOnSalary, ri.PlusSalary, 

ri.PayrollUnit, ri.ApptTenure, ri.AnniDate, ri.AgencyCode, ri.Schematic, ri.RollCode, ri.Shift, 

ri.CountyCode, ri.AddressLine1, ri.CityAndState, ri.FirstandMiddle, ri.ZipCode, 
 

CASE WHEN CHARINDEX(',', ri.CityAndState) > 0 

		--THIS IS THE LINE THAT CAUSES ERROR. Replace the THEN branch with something like: THEN 'test'  and it works fine.

		THEN left(ri.CityAndState, len(ri.CityAndState)-charindex(',',reverse(ri.CityAndState)))

		--RTRIM(LTRIM(LEFT(ri.CityAndState, CHARINDEX(',', ri.CityAndState + ' ') - 1)))

	 ELSE

		CASE WHEN

			CHARINDEX(' ', ri.CityAndState) > 0 THEN RTRIM(LTRIM(LEFT(ri.CityAndState, CHARINDEX(' ', ri.CityAndState + ' ') - 1)))

		ELSE

			ri.CityAndState

		END

END AS CITY,

CASE WHEN

		CHARINDEX(',', ri.CityAndState) > 0 THEN RTRIM(LTRIM(SUBSTRING(ri.CityAndState, CHARINDEX(',',ri.CityAndState) + 1, LEN(ri.CityAndState))))

	 ELSE

		CASE WHEN 

				CHARINDEX(' ', ri.CityAndState) > 0 THEN RTRIM(LTRIM(SUBSTRING(ri.CityAndState, CHARINDEX(' ',ri.CityAndState) + 1, LEN(ri.CityAndState)))) 

			 ELSE NULL 

		END 

END AS STATE
 
 

FROM FI_Temp_RosterImport ri

INNER JOIN FI_Rules r ON ri.RulesID = r.ID

WHERE	ri.JobID = 19

AND r.type = 'C'

Open in new window

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:WaldaInc
ID: 24064080
Here is another tidbit.

Original THEN branch is:

CASE WHEN CHARINDEX(',', ri.CityAndState) > 0
THEN left(ri.CityAndState, len(ri.CityAndState)-charindex(',',reverse(ri.CityAndState)))

Replace that with:

CASE WHEN CHARINDEX(',', ri.CityAndState) > 0
THEN left(ri.CityAndState, charindex(',',reverse(ri.CityAndState)))

And it runs fine (not correct results, but no error message). Also, using this works:

CASE WHEN CHARINDEX(',', ri.CityAndState) > 0
THEN left(ri.CityAndState, len(ri.CityAndState))

It just doesn't seem to like the Len(value) -  anothervalue portion.  That doesn't make sense to me, unless there is an invalid value coming back. But CHARINDEX return 0 if nothing found correct? And that shouldn't matter as we got to that branch BECAUSE we found the comma.




0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24064242
Ok.  Something didn't get copied correctly.

Because mine returns
new york|      NY|

Yours returns:
new|      york NY


And I cannot figure out what it could be.  There has to be a value in there that I'm not accounting for.  I, in my sample data, have tried:

","
""
" "
",something"
",something,"
" something"
"something "
"some,thing"

and nothing produces the error.  How many records are we talking about?
0
 

Author Comment

by:WaldaInc
ID: 24064305
How many records? The result set returns 239,000+ records.  There are 300,000+ in the table.

It is really weird. Just looking at that line of code, nothing stands out. By the way, below is the output from your code. It works fine as far as I can see:

san francisco, ca      san francisco      ca
new york NY      new york      NY
york, pa      york      pa
sacramento ca      sacramento      ca
pittsburgh      pittsburgh      NULL
0
 

Author Comment

by:WaldaInc
ID: 24064354
Give me a few minutes, I think I have the reason. Be back soon.

0
 

Author Comment

by:WaldaInc
ID: 24064439
Nope, no luck. It really doesn't like this:

len(ri.CityAndState)-charindex(',',reverse(ri.CityAndState)))

Now, I added some debugging code at the end of my select:

len(ri.CityAndState) AS LEN_CITY,
charindex(',',reverse(ri.CityAndState)) AS CHAR_INDEX

For one of the rows, these returned values of 10, 19 (respectively). I thought 10 - 19 is a negative number lets flop those like this:

left(ri.CityAndState, charindex(',',reverse(ri.CityAndState)) - len(ri.CityAndState))

But I get the same error. By the way, putting hardcoded integers in there works. (i.e 19 - 10).


0
 

Author Comment

by:WaldaInc
ID: 24064667
Ok, I think I found the problem.  This data in inserted into a temp table via a Bulk Insert, then copied over to the table I'm using. There appear to be just a bunch of spaces on the right and sometimes on the left of the data. I inserted a buch of LTRIm and RTRIM and that seems to work. Scanning the data I only see one row that comes out wrong and that row has no space OR comma in the field. Garbage in Garbage out I guess.

Code below
CASE WHEN CHARINDEX(',', ri.CityAndState) > 0 

	THEN 

		CASE WHEN LEN(ri.CityAndState) > 0

			THEN 

				RTRIM(LTRIM(LEFT(ri.CityAndState, CHARINDEX(',', ri.CityAndState + ' ') - 1)))			

			ELSE

				NULL

		END

	ELSE

		CASE WHEN CHARINDEX(' ', ri.CityAndState) > 0 

			THEN

				LTRIM(RTRIM(LEFT(ri.CityAndState, len(ri.CityAndState) - CHARINDEX(' ', reverse(RTRIM(ri.CityAndState)) ))))

			ELSE

				LTRIM(RTRIM(ri.CityAndState))

		END

END AS CITY,
 
 

CASE WHEN CHARINDEX(',', ri.CityAndState) > 0 

	THEN

		RTRIM(LTRIM(SUBSTRING(ri.CityAndState, CHARINDEX(',',ri.CityAndState) + 1, LEN(ri.CityAndState))))

	ELSE

		CASE WHEN CHARINDEX(' ', ri.CityAndState) > 0 

			THEN 

				rtrim(ltrim(right(rtrim(ri.CityAndState), charindex(' ',reverse(rtrim(ri.CityAndState))))))

			ELSE 

				NULL 

		END 

END AS STATE

Open in new window

0
 

Author Closing Comment

by:WaldaInc
ID: 31566332
Given you the points, you got me most of the way there and if I'd known about the spaces (i.e. posted that) you probably would have realized the issue sooner than I did.

Thanks
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24065471
That makes sense.  That is due to the fact of how len() works.  Len gives you the length of the string, minus any trailing spaces.  So what was happening if the number of characters from the end the comma existed at + amount of white space was equal to or greater than the number of characters - trailing spaces, you would get the error.

select len('test'),len('test   '),len('    test   '),len('   test')

The simple solution is to use datalength and not len().  Or, to trim the fields.  The reason I don't always jump for datalength() over len() is that datalength counts the number of bytes whereas len() counts the number of characters.  So if you have a unicode string, you have to do datalength(field)/2 to get the number of characters (including spaces).

Below is an updated solution using datalength that does not REQUIRE trimming.  Keep in mind, that if the fields are unicode (nvarchar) and not ascii (varchar) then you will have to add /2 after the datalength function to get the correct result.
select 

 

cityandstate

,case when charindex(',',cityandstate)>0 then left(cityandstate, datalength(cityandstate)-charindex(',',reverse(cityandstate)))

when charindex(' ',cityandstate)>0 then left(cityandstate, datalength(cityandstate)-charindex(' ',reverse(cityandstate)))

     else cityandstate

end

,case when charindex(',',cityandstate)>0  then ltrim(right(cityandstate, charindex(',',reverse(cityandstate))-1))

when charindex(' ',cityandstate)>0  then ltrim(right(cityandstate, charindex(' ',reverse(cityandstate))-1))

     else null

end

 ,

CASE WHEN CHARINDEX(',', ri.CityAndState) > 0 

                --THIS IS THE LINE THAT CAUSES ERROR. Replace the THEN branch with something like: THEN 'test'  and it works fine.

                THEN left(ri.CityAndState, datalength(ri.CityAndState)-charindex(',',reverse(ri.CityAndState)))

                --RTRIM(LTRIM(LEFT(ri.CityAndState, CHARINDEX(',', ri.CityAndState + ' ') - 1)))

         ELSE

                CASE WHEN

                        CHARINDEX(' ', ri.CityAndState) > 0 THEN RTRIM(LTRIM(LEFT(ri.CityAndState, CHARINDEX(' ', ri.CityAndState + ' ') - 1)))

                ELSE

                        ri.CityAndState

                END

END AS CITY,

CASE WHEN

                CHARINDEX(',', ri.CityAndState) > 0 THEN RTRIM(LTRIM(SUBSTRING(ri.CityAndState, CHARINDEX(',',ri.CityAndState) + 1, datalength(ri.CityAndState))))

         ELSE

                CASE WHEN 

                                CHARINDEX(' ', ri.CityAndState) > 0 THEN RTRIM(LTRIM(SUBSTRING(ri.CityAndState, CHARINDEX(' ',ri.CityAndState) + 1, datalength(ri.CityAndState)))) 

                         ELSE NULL 

                END 

END AS STATE

 

from (select 'san francisco, ca' as CityAndState

union all select 'new york, NY                      '

union all select 'york, pa'

union all select 'sacramento ca'

union all select 'pittsburgh'

union all select '') ri

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now