June 14, 2010 at 10:05 am
I have table called country having one column called complete_name (varchar(max))
which is as under
complete_name
Delaware -- United States (North America)
Maryland -- United States (North America)
Ontario- Canada (North America)
Utah- United States (North America)
lot of data like this
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:
complete name
region
country
state
Delaware -- United States (North America)
North America
United States
Delaware
Maryland -- United States (North America)
North America
United States
Maryland
Ontario- Canada (North America)
North America
canada
Ontario
Utah- United States (North America)
North America
United States
utah
etc lot of data like this
This is very urgent.Please reply me back asap
June 14, 2010 at 10:33 am
I have run out of time today but here is a start:
CREATE TABLE CountryFull
(
ItemDescription VARCHAR(99)
)
INSERT CountryFull
VALUES ( 'RandomText Delaware USA, RandomText Delaware USA' )
INSERT CountryFull
VALUES ( 'Montreal CANADA RandomText, Montreal CANADA' )
INSERT CountryFull
VALUES ( 'Texas USA RandomText, Texas USA' )
INSERT CountryFull
VALUES ( 'RandomText London UK RandomText, RandomText London UK' )
SELECT RIGHT(ItemDescription, PATINDEX('% %', REVERSE(ItemDescription)) - 1) AS COL1
, LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,
( LEN(ItemDescription) - PATINDEX('% %', REVERSE(ItemDescription)) ))),
PATINDEX('% %',
REVERSE(LEFT(ItemDescription,
( LEN(ItemDescription) - PATINDEX('% %', REVERSE(ItemDescription)) ))))))) AS COL2
FROM CountryFull
DROP TABLE CountryFull
The patindex is looking for spaces in the full description and then separating them into columns. You could do something similar with the "-" and combine that with spaces and/or case sensitive checks.
gsc_dba
June 14, 2010 at 10:34 am
June 14, 2010 at 10:39 am
If you let your homework go too long, it will become urgent. We are all volunteers here on this site so it would help us if you would do the inital work on something like this and provide us with table DDL, sample data (in the form of insert statements), and any SQL code that you have already tried. We are not here to do all of the work for you. Escpecially if this is homework.
Why would we want to help you cheat your way into our profession? Why would we want to help someone get into our line of work who is not willing to make an attempt at solving their own problems?
SSC is a great resource for SQL Server so take advantage of that, just don't take advantage of those who are willing to help.
June 14, 2010 at 10:45 am
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
declare @test-2 table (Col1 varchar(100))
insert into @test-2
select 'Delaware -- United States (North America)' UNION ALL
select 'Maryland -- United States (North America)' UNION ALL
select 'Ontario- Canada (North America)'
;WITH CTE AS
(
select Col1,
Pos0 = CHARINDEX('-', Col1),
Pos1 = CHARINDEX('- ', Col1),
Pos2 = CHARINDEX('(', Col1),
Pos3 = CHARINDEX(')', Col1)
FROM @test-2
)
SELECT Col1,
Region = SUBSTRING(Col1, Pos2+1, Pos3-Pos2-1),
Country = SUBSTRING(Col1, Pos1+1, Pos2-Pos1-1),
State = SUBSTRING(Col1, 1, Pos0-1)
FROM CTE
edit: added sql code tags
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 15, 2010 at 2:03 am
John Rowan (6/14/2010)
If you let your homework go too long, it will become urgent. We are all volunteers here on this site so it would help us if you would do the inital work on something like this and provide us with table DDL, sample data (in the form of insert statements), and any SQL code that you have already tried. We are not here to do all of the work for you. Escpecially if this is homework.Why would we want to help you cheat your way into our profession? Why would we want to help someone get into our line of work who is not willing to make an attempt at solving their own problems?
SSC is a great resource for SQL Server so take advantage of that, just don't take advantage of those who are willing to help.
Calm down FFS
June 15, 2010 at 2:04 am
WayneS (6/14/2010)
-- See how this starts off with a table and data in it?-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
declare @test-2 table (Col1 varchar(100))
insert into @test-2
select 'Delaware -- United States (North America)' UNION ALL
select 'Maryland -- United States (North America)' UNION ALL
select 'Ontario- Canada (North America)'
;WITH CTE AS
(
select Col1,
Pos0 = CHARINDEX('-', Col1),
Pos1 = CHARINDEX('- ', Col1),
Pos2 = CHARINDEX('(', Col1),
Pos3 = CHARINDEX(')', Col1)
FROM @test-2
)
SELECT Col1,
Region = SUBSTRING(Col1, Pos2+1, Pos3-Pos2-1),
Country = SUBSTRING(Col1, Pos1+1, Pos2-Pos1-1),
State = SUBSTRING(Col1, 1, Pos0-1)
FROM CTE
edit: added sql code tags
Nice idea that one, keeping that in my "bank" of code, thanks. 🙂
June 15, 2010 at 2:40 am
Very elegant solution!
I "played around" with the positions but couldnt remove the leading space without this:
Country = LTRIM(SUBSTRING(Col1, Pos1+1, Pos2-Pos1-1))
gsc_dba
June 15, 2010 at 9:54 am
Cowboy DBA (6/15/2010)
John Rowan (6/14/2010)
If you let your homework go too long, it will become urgent. We are all volunteers here on this site so it would help us if you would do the initial work on something like this and provide us with table DDL, sample data (in the form of insert statements), and any SQL code that you have already tried. We are not here to do all of the work for you. Especially if this is homework.Why would we want to help you cheat your way into our profession? Why would we want to help someone get into our line of work who is not willing to make an attempt at solving their own problems?
SSC is a great resource for SQL Server so take advantage of that, just don't take advantage of those who are willing to help.
Calm down FFS
FFS? Please explain.
This is clearly not an urgent matter for the OP. If you look back at previous posts from the OP, the other post was another 'urgent' homework assignment. Helping people cheat the system by doing their homework for them is not advancing our community in any way. I am more than willing to help someone solve a SQL problem, homework or not, as long as they are making any attempt to learn and figure it out on their own. This OP has clearly not demonstrated that so helping by providing full solutions for them is not really helping at all. Do you want your company to hire a junior DBA who just graduated college but does not understand the fundamentals of SQL because they did not do any of the homework?
June 15, 2010 at 10:05 am
One guess is that FFS = For F___'s Sake. Let's not speculate as to the missing f-word.
Posting answers for homework assignments does nothing to further the community, or the education of the individual. Also, an online forum is not the best place to seek help for "urgent" questions. John's response seemed perfectly calm and reasonable to me.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 15, 2010 at 10:12 am
He must be the best student in his year if he provides the homework made by SSC community... :hehe:.
Having such DBA in the company helps a lot! As it gurantees the work for poor contractors 😀
June 16, 2010 at 2:32 am
John Rowan (6/15/2010)
FFS? Please explain.
This is clearly not an urgent matter for the OP. If you look back at previous posts from the OP, the other post was another 'urgent' homework assignment. Helping people cheat the system by doing their homework for them is not advancing our community in any way. I am more than willing to help someone solve a SQL problem, homework or not, as long as they are making any attempt to learn and figure it out on their own. This OP has clearly not demonstrated that so helping by providing full solutions for them is not really helping at all. Do you want your company to hire a junior DBA who just graduated college but does not understand the fundamentals of SQL because they did not do any of the homework?
TBH I don't really care, I'm confident enough in my knowledge and ability to learn new stuff. 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply