Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sonu619
Posting Yak Master
202 Posts |
 Posted - 2011-07-27 : 21:04:13
|
| Hi guys, Here is my dataAdd,Street_Number,Street_Name123 Johnson St,Null,Null22 Main Blvd, Null, Null I want to split "Add" column end result should be like this Add,Street_Number,Street_Name123 Johnson St,123,Johnson22 Main Blvd, 22, Main BlvdPlease guide me how i can accomplish this one in t-sql. Thanks. |
|
|
marek_gd
Starting Member
6 Posts |
Posted - 2011-07-31 : 17:17:20
|
| Hi Sonu619,The code below should help:Copy and paste and see how it works.select'22 Main Blvd',CHARINDEX(' ','22 Main Blvd'),LEFT('22 Main Blvd',CHARINDEX(' ','22 Main Blvd')),SUBSTRING('22 Main Blvd',CHARINDEX(' ','22 Main Blvd'),LEN('22 Main Blvd'))First you should use 'CHARINDEX' to identify first space. As a result you will receive number corresponding to first space: in this case this is 3.Next, use 'LEFT' to keep only the left part of a character string, number of characters returned is 3, because CHARINDEX(' ','22 Main Blvd') = 3Finally use SUBSTRING to hold only interesting part of the whole character string expression, starting from 3-rd character (=CHARINDEX(' ','22 Main Blvd') ) and finishing with the last character (LEN('22 Main Blvd')).LEN returns the number of characters of the specified string expression, excluding trailing blanks.Hope that helps, |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-01 : 04:15:52
|
| one thing to note is your address format should be consistent for splitting to work correctly.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Split Column