[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql charindex

Posted on 2011-10-27
15
Medium Priority
?
495 Views
Last Modified: 2012-08-13
I want to split my address into two separate fields. My address will be as follows ambleside close, Blackburn. Comma as the separator.

Needs to be split into fields addressline2 and addressline3.

I know you can use charindex but not sure how to implement it.


0
Comment
Question by:aneilg
  • 6
  • 6
  • 3
15 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37037234
substring(address,1,charindex(',',address)-1) as addressline2,  substring(address,charindex(',',address)+1,9999) as addressline3
0
 

Author Comment

by:aneilg
ID: 37037735
thanks for that but i get an error Invalid length parameter passed to the SUBSTRING function.

because i will be returning null values, where in the tsql do i put isnull.

thanks;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37037793
you can wrap the entire expression in ISNULL if you want.  I can't say for sure since I don't know what result you're looking for when address is NULL

also, the error is probably because some of your addresses don't have a comma in them.

if that happens,  charindex will return 0,  meaning your substring length will be -1 for addressline2  which is an error.

when you don't have a comma, what do you want the results to be?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 500 total points
ID: 37038007
SELECT SUBSTRING(address, 1, CHARINDEX(',', address)-1) as addressline2,  SUBSTRING(address, CHARINDEX(',', address)+1,LEN(address)) as addressline3
FROM [your_table_name]
WHERE address IS NOT NULL AND LEN(address) > 0 AND CHARINDEX(',' address) > 0
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37038018
http:#37038007  simply uses my solution above with the assumption that you don't want to return any NULL values or blank values and you also want to exclude anything that doesn't have a comma

based on your comments above I don't think that's the case though.

please elaborate
0
 
LVL 5

Expert Comment

by:eridanix
ID: 37038100
struber: My answer is one of the possible solutions.
I mean, that my select is only one part of whole problem.
There will be some insert or update after aneilg will check the select.

I know my comment is similar you first comment, but there is no more possible options to split strings in SQL, if you don't want to use some stored procedure or function.
And using 9999 as max possible length of string is not much propper use.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37038143
sorry,  I meant "please elaborate" for the asker.

I understand your suggestion fine.  I was looking for clarification from the asker what they wanted, otherwise we're just guessing.

my original post will do what they asked for (possibly adjusting the 9999 if needed or using len())
but there are apparently additional requirements.
0
 
LVL 5

Expert Comment

by:eridanix
ID: 37038176
OK, I am sorry, too! :-) It was my misunderstanding...
0
 

Author Comment

by:aneilg
ID: 37038300
hello thanks for you help guys, addressline2 will always have data, but i think the problem is when there is no other address.

eg addressline2 will have data as 'ambleside', but as you can see there is no comma or another address. this is were the problem is.

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37038320
so,  if there is no comma, you only want addressline2 populated and leave addressline3 null?
if so...

case when charindex(',',address) > 0 then
    substring(address,1,charindex(',',address)-1)
else
   address
end
 as addressline2,
case when charindex(',',address > 0 then
  substring(address,charindex(',',address)+1,9999)
else
   null
end as addressline3

as mentioned above you can use len(address)  instead of 9999  
 I used that simply to indicate "something big so you read to end of string"
0
 

Author Comment

by:aneilg
ID: 37038348
i am trying  , CASE WHEN CHARINDEX(',',ISNULL(column),table)) > 0
      THEN LEFT(ISNULL(column),table),CHARINDEX(',',ISNULL(column),table))-1)
      ELSE ISNULL(column),table)
      END                                                AS ADDRESS_LINE_2      --12
bu ti get errors
0
 

Author Comment

by:aneilg
ID: 37038388
i think that might be it 'replace table for the column 'for the first addressline2, now just need to figure addressline3.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37038406
why are you using ISNULL ?

http:#37038300   -  "addressline2 will always have data"

which means your source column will always have data too  so checking for NULL is not only unnecessary it adds complexity


did  you try what I posted?  if you did and it didn't work,  please post sample data and expected results that differ from what those returned
0
 

Author Comment

by:aneilg
ID: 37043646
thanks guys with your help i managed to get i tto work.
0
 

Author Closing Comment

by:aneilg
ID: 37043676
thanks for your help guys.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 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