Finding '/' positions
-
I have a URL column as follows
URL
http://www.sqltest.com/Forums/Topic/
http://biz.yahoo.com/topic/invest/test-1/
I would like to find each ‘/’ position (at most six positions) and make a 'position' table like below.
Position
pos_1, pos_2, pos_3, pos_4, pos_4, pos_5, pos_6
6,7,23,30,36
6,7,21,27,33,39
Does anyone have a method to automate CHARINDEX function and make a table above?
-
Hi
You can use a sub-query in your SELECT clause using a Tally (aka Numbers) table to find the positions. Use FOR XML PATH to concatenate the positions and SUBSTRING to remove the first ", ".
If you don't know what's a Tally table search this site for Jeff Moden's article.
[font="Courier New"]DECLARE @t TABLE (Id INT NOT NULL IDENTITY, Url VARCHAR(256))
INSERT INTO @t
SELECT 'http://www.sqltest.com/Forums/Topic/'
UNION ALL SELECT 'http://biz.yahoo.com/topic/invest/test-1/'
SELECT
t1.Url,
SUBSTRING(
(
SELECT ', ' + CONVERT(VARCHAR(10), N)
FROM Tally
WHERE N <= LEN(t1.Url)
AND SUBSTRING(t1.Url, N, 1) = '/'
FOR XML PATH('')
),
2,
LEN(t1.Url)
)
FROM @t t1
[/font]
Greets
Flo
-
Have a look at Jeff Moden's article [p]http://www.sqlservercentral.com/articles/TSQL/62867/[/p]
on the uses for a tally table. You should be able to use this to parse strings for character positions.
-
Hi, Florian Reischl
Thanks for comments.I was wrong showing the problem posted. Could you help me again?
I have a URL column as follows
URL table
http://www.sqltest.com/Forums/Topic/
http://biz.yahoo.com/topic/invest/test-1/
I would like to find each '/' position like CHARINDEX function.
There are a lots of positions at URL data.
For example, the below are original source. I would like to know better and efficient way.
select url, CHARINDEX('/', url) as first_slash
into url_1 from url;
go
select url, first_slash, CHARINDEX('/', url, first_slash+1) as second_slash
into url_2 from url_1;
go
select url, first_slash, second_slash, CHARINDEX('/', url, second_slash+1) as third_slash
into url_3 from url_2;
go
select url, first_slash, second_slash, third_slash, CHARINDEX('/', url, third_slash+1) as fourth_slash
into url_4 from url_3;
go
select url, first_slash, second_slash, third_slash, fourth_slash, CHARINDEX('/', url, fourth_slash+1) as fifth_slash
into url_5 from url_4;
go
select url, first_slash, second_slash, third_slash, fourth_slash, fifth_slash, CHARINDEX('/', url, fifth_slash+1) as sixth_slash
into url_6 from url_5;
go
select * into url_data from url_6;
go
drop table url_1;
drop table url_2;
drop table url_3;
drop table url_4;
drop table url_5;
drop table url_6;
go
-
Hi ohio_bear
Funny requirement
First use a modified version of the split function above to get the slash positions into a table. After that you can use a PARTITION row-number or ranking and a PIVOT to bring the results back to columns.
[font="Courier New"]DECLARE @url TABLE (Id INT NOT NULL IDENTITY, Url VARCHAR(256))
DECLARE @url_split TABLE (Id INT NOT NULL IDENTITY, Url VARCHAR(256), Pos INT)
-- Some test data
INSERT INTO @url
SELECT 'http://www.sqltest.com/Forums/Topic/'
UNION ALL SELECT 'http://biz.yahoo.com/topic/invest/test-1/'
-- Split url by '/'
INSERT INTO @url_split
SELECT
t1.Url,
pos.N
FROM @url t1
CROSS APPLY
(
SELECT
N
FROM dbo.Tally
WHERE N <= LEN(t1.Url)
AND SUBSTRING(t1.Url, N, 1) = '/'
) pos
; WITH
url_slash (Url, SlashSequence, Pos) AS
(
-- Get the partition ranking for the slashes
SELECT
Url,
ROW_NUMBER() OVER (PARTITION BY Url ORDER BY Pos),
Pos
FROM @url_split
)
SELECT
*
FROM url_slash
-- Pivot by slash sequence
PIVOT
(
MIN(Pos)
FOR SlashSequence IN ([1], [2], [3], [4], [5], [6], [7], [8])
) pvt[/font]
Greets
Flo
-
Hi, Florian Reischl
I tried to run your script. But, it did not work.
Output is:
url 0 1 2 3 4 5 6 7 8
There are no data at the output.
Does my script work?
Please see below
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
CREATE TABLE dbo.Tally
(N INT,CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))
DECLARE @url TABLE (Id INT NOT NULL IDENTITY, Url VARCHAR(256))
DECLARE @url_split TABLE (Id INT NOT NULL IDENTITY, Url VARCHAR(256), Pos INT)
-- Some test data
INSERT INTO @url
SELECT 'http://www.sqltest.com/Forums/Topic/'
UNION ALL SELECT 'http://biz.yahoo.com/topic/invest/test-1/'
-- Split url by '/'
INSERT INTO @url_split
SELECT
t1.Url,
pos.N
FROM @url t1
CROSS APPLY
(
SELECT
N
FROM dbo.Tally
WHERE N <= LEN(t1.Url)
AND SUBSTRING(t1.Url, N, 1) = '/'
) pos
; WITH
url_slash (Url, SlashSequence, Pos) AS
(
-- Get the partition ranking for the slashes
SELECT
Url,
ROW_NUMBER() OVER (PARTITION BY Url ORDER BY Pos),
Pos
FROM @url_split
)
SELECT
*
FROM url_slash
-- Pivot by slash sequence
PIVOT
(
MIN(Pos)
FOR SlashSequence IN ([1], [2], [3], [4], [5], [6], [7], [8])
) pvt
-
Hi
Your Tally table does not contain any numbers
Put this code above your URL-split:
INSERT INTO dbo.TallySELECT TOP (11000)ROW_NUMBER() OVER (ORDER BY (SELECT NULL))FROM master.sys.all_columns c1CROSS JOIN master.sys.all_columns c2Greets
Flo
-
Florian Reischl (5/17/2009)
Put this code above your URL-split:ROW_NUMBER() OVER (ORDER BY (SELECT NULL))I say it all the time, I learn something every day on this site.
.
It never occurred to me that the over(ORDER BY... clause could use a null or static value in the Over definition.... up to now, I just assumed i had to have a column from the table.
once again, the light goes on. thanks!
I honestly do not remember how we did the equivalent of row_number or ranking in 2000 anymore...sheesh.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank!!!
It worked great!!
Thank you very much!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply