Home > IT Answers > SQL Server > How to split a column into SQL data..
Anamika83
15 pts.
 How to split a column into SQL data..
SQL Server 2005, SQL Server tables, T-SQL
I have table called  country having one column called complete_name (varchar(max))

which is as under
 
[table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0"> [tbody] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 6.15in;" valign="top" width="590"> [strong]complete_name[/strong]

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 6.15in;" valign="top" width="590">  Delaware -- United States (North America)

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 6.15in;" valign="top" width="590"> Maryland -- United States (North America)

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 6.15in;" valign="top" width="590"> Ontario- Canada (North America)

 

 

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 6.15in;" valign="top" width="590"> Utah- United States (North America)

 

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 6.15in;" valign="top" width="590"> lot of data like this

[/td] [/tr] [/tbody] [/table] I need to split this one column into four column as under;

 

column1:same as above

col2:region(varchar(max)

col3:country(varchar(max)

col4:state(varchar(max))

 

Desired output is as under:

 

 

 

 

[table style="margin: auto 6.75pt; width: 564pt; border-collapse: collapse;" align="left" border="0" cellpadding="0" cellspacing="0" width="752"> [tbody] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 240pt;" valign="top" width="320"> [strong]complete name[/strong]

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 96pt;" valign="top" width="128"> [strong]region[/strong]

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 1.75in;" valign="top" width="168"> [strong]country[/strong]

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 102pt;" valign="top" width="136"> [strong]state[/strong]

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 240pt;" valign="top" width="320">  Delaware -- United States (North America)

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 96pt;" valign="top" width="128"> North America

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 1.75in;" valign="top" width="168"> United States

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 102pt;" valign="top" width="136"> Delaware

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 240pt;" valign="top" width="320"> Maryland -- United States (North America)

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 96pt;" valign="top" width="128"> North America

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 1.75in;" valign="top" width="168"> United States

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 102pt;" valign="top" width="136"> Maryland

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 240pt;" valign="top" width="320"> Ontario- Canada (North America)

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 96pt;" valign="top" width="128"> North America

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 1.75in;" valign="top" width="168"> canada

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 102pt;" valign="top" width="136"> Ontario

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 240pt;" valign="top" width="320"> Utah- United States (North America)

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 96pt;" valign="top" width="128"> North America

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 1.75in;" valign="top" width="168"> United States

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 102pt;" valign="top" width="136"> utah

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 240pt;" valign="top" width="320"> etc lot of data like this

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 96pt;" valign="top" width="128">  

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 1.75in;" valign="top" width="168">  

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 102pt;" valign="top" width="136">
 
 
[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 240pt;" valign="top" width="320">  

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 96pt;" valign="top" width="128">  

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 1.75in;" valign="top" width="168">  

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 102pt;" valign="top" width="136">  

[/td] [/tr] [tr] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 240pt;" valign="top" width="320">  

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 96pt;" valign="top" width="128">  

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 1.75in;" valign="top" width="168">  

[/td] [td style="border: medium none #d4d0c8; padding: 0in 5.4pt; background-color: transparent; width: 102pt;" valign="top" width="136">  

[/td] [/tr] [/tbody] [/table]  

[Br clear="all" />This is very urgent.Please reply me back asap

Software/Hardware used:
SQL server 2005 - T-SQL
ASKED: June 14, 2010  4:09 PM
UPDATED: June 24, 2010  12:40 AM
RELATED QUESTIONS
Answer Wiki:
This problem is why relational data is best reduced to a more atomic level. A better design would be something like: <pre> Table: LOCATION columns STAT_PROV COUNTRY CONTINENT </pre> When you need them combined, you can do so in your SQL. Like this: SELECT STAT_PROV || '--' || COUNTRY || '(' || CONTINENT || ')' FROM ... Think of a person's name. You wouldn't store it as one long string (probably). Instead the components would be sotred as FIRST, MIDDLE, LAST, ADDR (e.g. Sir, MR), SUFFIX (e.g. JR., III) OK, back to your existing problem. Delaware -- United States (North America) Can we rely on the hyphens and the parentheses? If they will ALWAYS be there, then we can parse the components by looking for them. Does your RDBMS SQL have a LOCATE function or something like it? DB2's LOCATE works like this: LOCATE(string1 , string2) will return the location of "string1" within "string2" You could use something like this to get the offsets of the hyphens and the parens. SELECT LOCATE('--', COMPLETE_NAME) , LOCATE('(' , COMPLETE_NAME), LOCATE( ')' , COMPLETE_NAME) Then use the host_variables as part of a SUBSTRING function to actually obtain the data. SELECT SUBSTRING(COMPLETE_NAME , 1 , value_from_locate - 1) AS STAT_PROV Perhaps you could even put the LOCATE function within the SUBSTRING function. I am not sure. I did not test this out. Almost certainly you could make the LOCATEs apart of the SELECT. Perhaps like this: SELECT SUBSTRING(COMPLETE_NAME , 1 , S.A - 1) AS STATE_PROV FROM table , (SELECT LOCATE ( '--', COMPLETENAME) as A ) as S I hope this gets you started. But I recommend that the design of the data be considered more carefully in the future. -- Using T-SQL (Sql Server), the <a href="http://technet.microsoft.com/en-us/library/ms186323.aspx">CHARINDEX function</a> can be used. The command would be something like this: <pre>SELECT SUBSTRING(complete_name,1,CHARINDEX('-',complete_name)-1) AS state, SUBSTRING(complete_name,CHARINDEX('-',complete_name) ... SUBSTRING(complete_name,CHARINDEX('(',complete_name) ... FROM ... WHERE ...</pre>
Last Wiki Answer Submitted:  June 24, 2010  12:40 am  by  Meandyou   5,205 pts.
All Answer Wiki Contributors:  Meandyou   5,205 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _