Seperate combined data? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Seperate combined data?

Author

Topic

mflammia
Starting Member

44 Posts

Posted - 2008-07-21 : 04:51:00

Appreciate all your help as always...

Have a DB that has entries as per below:

The ProprietorTel: 01256 321491www.marshtrunpark.co.uk
South Hall MuseumTel: 01708 467671www.southmuseums.co.uk
ProprietorTel: 01281 606505
The Manager
NULL

What I would like to do is separate the information out into their respective columns, like Name, Number and URL.

Its easy to see the number can be found with anything starting with Tel: and the URL starting with www. My amateur thoughts where that I could separate this information the same way I did with a delaminated list, using the Parsname or FnParsname functions, but this did not work as the T in Tel: and the W in www would be the first part it matched instead of the whole phrase, so I got the wrong results.

Is there any other way to do this?

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 04:53:02

use charindex() to find the location of "Tel" and "www" then use left() or substring() or right() to extract the required string

KH
[spoiler]Time is always against us[/spoiler]

mflammia
Starting Member

44 Posts

Posted - 2008-07-21 : 09:53:33

Thanks for the info. Ok, I've had a crack at this but being a newbie I feel I'm doing something completely wrong.

DECLARE @TABLE TABLE
(
col varchar(1000)
)
INSERT INTO @TABLE (col)
SELECT _Contact
FROM DBname.dbo._DATA

SELECT col,
ext1 = CASE WHEN CHARINDEX('Tel:', col) > 0
THEN left(col, CHARINDEX('Tel:', col) - 1)
ELSE col
END
, ext2 = CASE WHEN CHARINDEX('www', col) > 0
THEN right(col, CHARINDEX('www', col) - 2)
ELSE col
END
,ext3 = CASE WHEN CHARINDEX('Tel:', col) > 0
THEN right(col, CHARINDEX('Tel:', col) - 1)
ELSE col
END
FROM @TABLE

Below (apologise for not representing it better) is the results. As you can see from the source data in Col the results are not being broken up as expected, any idea why?

Col ¦ext1 ¦ext2 ¦ext3

ProprietorTel: 01245 606555 ¦ Proprietor ¦ ProprietorTel: 01245 606555 ¦ 245606555

Adventure landTel: 01702 443444www.adventureland.co.uk ¦ Adventure Island ¦ 443444www.adventureland.co.uk¦ tureland.co.uk

Many thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 10:03:53

[code]SELECT Col,
CASE WHEN CHARINDEX(':',Col)>0 THEN LEFT(Col,CHARINDEX(':',Col)-1)
ELSE Col
END AS ext1,
CASE WHEN CHARINDEX(':',Col)>0 AND PATINDEX('%www%',Col)>0 THEN LEFT(SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col)), PATINDEX('%www%',SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col)))-1)
ELSE SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col))
END AS ext2,
CASE WHEN CHARINDEX(':',Col)>0 AND PATINDEX('%www%',Col)>0 THEN
LEFT(SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col)), PATINDEX('%www%',SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col))),LEN(Col))
ELSE NULL
END AS ext3
FROM YourTable[/code]

mflammia
Starting Member

44 Posts

Posted - 2008-07-21 : 10:09:46

Can't beleive you can all come up with this stuff so quick and easy - have a long way to go to learn!

Thanks, but a have a small error and unable to see where the left function is missing an argument?

Msg 174, Level 15, State 1, Line 17
The left function requires 2 argument(s).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 10:18:19

[code]SELECT Col,
CASE WHEN CHARINDEX(':',Col)>0 THEN LEFT(Col,CHARINDEX(':',Col)-1)
ELSE Col
END AS ext1,
CASE WHEN CHARINDEX(':',Col)>0 AND PATINDEX('%www%',Col)>0 THEN LEFT(SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col)), PATINDEX('%www%',SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col)))-1)
ELSE SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col))
END AS ext2,
CASE WHEN CHARINDEX(':',Col)>0 AND PATINDEX('%www%',Col)>0 THEN
SUBSTRING(Col,PATINDEX('%www%',Col),LEN(Col1))
ELSE NULL
END AS ext3
FROM YourTable[/code]

mflammia
Starting Member

44 Posts

Posted - 2008-07-21 : 10:25:23

Fantastic! Thank you.

mflammia
Starting Member

44 Posts

Posted - 2008-10-05 : 06:10:40

Sorry, there is one example I forgot to add and that was an entry in the DB that appears like this:

Mrs Mary Robinwww.Hertford.co.uk/foodmarkets

This is not being separated into its respective column.

Have tried playing around with the query to make this work without any luck - although the answer is probably not difficult but more out the reach of my limited knowledge.

Grateful for any help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 06:26:35

[code]SELECT Col,
CASE WHEN CHARINDEX(':',Col)>0 THEN LEFT(Col,CHARINDEX(':',Col)-1)
WHEN PATINDEX('%www%',Col)>0 THEN LEFT(Col,PATINDEX('%www%',Col)-1)
ELSE Col
END AS ext1,
CASE WHEN CHARINDEX(':',Col)>0 AND PATINDEX('%www%',Col)>0 THEN LEFT(SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col)), PATINDEX('%www%',SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col)))-1)
ELSE SUBSTRING(Col,CHARINDEX(':',Col)+1,LEN(Col))
END AS ext2,
CASE WHEN ~~CHARINDEX(':',Col)>0 AND~~ PATINDEX('%www%',Col)>0 THEN
SUBSTRING(Col,PATINDEX('%www%',Col),LEN(Col1))
ELSE NULL
END AS ext3
FROM YourTable[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources