How to split a column into SQL data..

15 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

This problem is why relational data is best reduced to a more atomic level. A better design would be something like:

Table: LOCATION
columns
STAT_PROV
COUNTRY
CONTINENT

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 CHARINDEX function can be used.

The command would be something like this:

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 ...

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following