Solved

SQL Syntax

Posted on 2009-04-03
16
282 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle - Stored Procedure Privilge access 3 39
mySQL Syntax 7 34
SQL Statement to Update Email Domain 2 21
Join multiple pivot queries 2 8
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

15 Experts available now in Live!

Get 1:1 Help Now