April 13, 2005 at 11:46 am
Hi. The address field of customer has two lines like
Suite B
500 Main Street combined in one field street
How can I do a query to seperate those two lines into two seperate columns ?
Thank you for your help.
This is what I tried and didnt' work
select substring(street,1,'\n') as address1, substring (street,'\n', 100) as address2.
April 13, 2005 at 11:59 am
Try searching for Char(10) <Line Feed> and/or Char(13) <Carriage Return>.
Use Left(Address, CharIndex(Address, Char(10))) for the first section.
April 13, 2005 at 12:18 pm
Hi
Thank you for the suggestion, I tried it but MS SQL server doesn't like it, error message: The data type text is invalid for charindex function
April 13, 2005 at 12:45 pm
I tried this charindex (char(10), street,2) and the function gives me the position of the new line character but when the address has only one line, the function returns 0, so I can't do the substring function "SUBSTRING(street, CHARINDEX(CHAR(10), street, 3), 20)" to find the second part. This function gives me the top part if address has one line, and it gives me the second part if address has two lines.
Any help would be appreciated.
April 14, 2005 at 8:20 am
I apolagize for not having the complete code for this but I don't have much time this morning while my internet is still up and I'm trying to handle as much email as I can. Still I wanted to add some comments to this that I believe will take you in the right direction.
What you are describing is perfect for a UDF (User-Defined Fuction). There are 2 different ways to use a UDF for this. You can either all it within the query and set it to return a scalar value which is either the first or second line of the address. The sceond option is to create a table valued UDF that returns 3 colums. One of the columns contains a key value to link the table returned by the UDF to the table that contains the address info you are parsing and a column for the first address line and a column for the second address line.
Which do you use? Depends on your circumstances. The scalar UDF is nice in that you can use it anwywhere in your query that a scalar UDF is acceptable. The problem with this approach is that it could impact performance more then is tolerable. You will have to call the UDF twice for every record in order to get bot the first and second address lines. If you go with the table valued UDF then the the UDF is called only once as oppsed to once for every record. You can link up the UDF table to the main table via the key column and then ini the SELECT clause of your query you list the other 2 columns form the table. The downside here is that this means adding another table and every table included in a query affects performance.
So how do you choose? The only way to truely implement the best one is to create both and test them. Which ever one results in the least amount of Reads/Writes and other query performance statistics should be the one you go with so long as it does what you need. By that I mean if teh table valued UDF is the best iin terms of performance but is not flexable enough for whatever reason then you have to decide is teh trade off in performance by going to the scalar UDF is worth the extra flexability it offers.
I'm not going to kid you and say that the UDF is the best choice for you. I can't nor can anyone else unless they know your data and your setup. I can tell you that this is a perfect example of why the UDF was created. To allow for perfoming complex calculations for use iin a query that would normally be difficult to ull off via standard SQL syntax. Using a UDF has additional advantages that using standard SQL SYntax such as the Substring() function doesn't. It's easy to reuse in other queries and if you find you need to change the logic or structure you need only do so in one place, the definition of the UDF.
Kindest Regards,
Just say No to Facebook!Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply