Remove string before or after a specified character

[katehol]

I need to split up some web addresses that are being stored in my database. I currently have strings in the format of

{name}/web/guest/home

I want to be able to split this into 2 columns - one for the name and one for the remainder of the web page, so that I can count individual users and individual page hits. So I would end up with one new column called 'name' (with all of the names in brackets{}) and one new column called page (with just the trailing web page address).

Am totally stuck on this, so any help would be appreciated.

Thanks

 

[HarleyQuinn]

Hi,

How about something like:

SELECT
Left(YourWebAddress, InStr(1, YourWebAddress, "/") - 1) as WebName,
Right(YourWebAddress, Len(YourWebAddress) - (InStr(1, YourWebAddress, "/") - 1)) as WebPage
FROM YourTable...

It's not the most elegant though

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[katehol]

Thanks for that - unfortunately, InStr is not recognised in SQL Server.

 

[HarleyQuinn]

Sorry, my mistake I forgot which forum I was in there

You can use CHARINDEX() to find the position of the / in the column rather than INSTR()

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[katehol]

Thanks Harley - worked a treat

Used this in the end

SELECT
Left(YourWebAddress, CHARINDEX('/',YourWebAddress) - 1) as WebName,
Right(YourWebAddress, Len(YourWebAddress) - (CHARINDEX('/',YourWebAddress) - 1)) as WebPage
FROM YourTable

 

[HarleyQuinn]

Glad I could help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.