February 23, 2015 at 12:00 pm
create table page
(
id int,
webpage varchar(max)
)
insert into values(id,webpage)values(1,'/user/advisory'),
(2,'/user/alerts')
actual output will be :
id webpage
1 advisory
2 alerts
February 23, 2015 at 12:05 pm
Do you always just want to replace /user/ or do you always just want everything after the final /
February 23, 2015 at 12:06 pm
do you want to get just whatever is to the right of the last slash, or just remove /user/?
/*--results
Samp1(No column name)
advisoryadvisory
Peter/ParkerParker
Harry/OsbornOsborn
Mary/Jane WatsonJane Watson
John/Jonah/Jameson Jr.Jameson Jr.
*/
create table #MyData
(
id int identity(1,1),
webpage varchar(max)
)
insert into #MyData(webpage)
SELECT '/user/advisory' UNION ALL
SELECT '/user/Peter/Parker' UNION ALL
SELECT '/user/Harry/Osborn' UNION ALL
SELECT '/user/Mary/Jane Watson' UNION ALL
SELECT '/user/John/Jonah/Jameson Jr.'
SELECT REPLACE(webpage,'/user/','') As Samp1,
REVERSE(LEFT(REVERSE(webpage),CHARINDEX('/',REVERSE(webpage))-1) )
from #MyData
Lowell
February 23, 2015 at 12:07 pm
If you just want to replace /user/ do
select id, replace(webpage,'/user/','') from page
February 23, 2015 at 12:31 pm
some time column value will be :
id webpage
1 /user/advisory/vv2
2 /user/alert/abc
output will be:
id webpage
1 advisory
2 alert
February 23, 2015 at 12:46 pm
select id,right(webpage,charindex('/',reverse(webpage))-1) from page
February 23, 2015 at 1:04 pm
not getting ...........
February 23, 2015 at 5:44 pm
create table #page
(
id int,
webpage varchar(max)
)
insert into #page(id,webpage)values(1,'/user/advisory'),
(2,'/user/alerts'),(3,'user/advisory/vv2');
SELECT id,webpage
,s=CASE CHARINDEX('/', c.s) WHEN 0 THEN c.s ELSE LEFT(c.s, CHARINDEX('/', c.s)-1) END
FROM #page a
CROSS APPLY
(
SELECT CASE CHARINDEX('/', webpage) WHEN 1 THEN webpage ELSE '/' + webpage END
) b (s)
CROSS APPLY (SELECT STUFF(s, 1, 6, '')) c(s)
GO
DROP TABLE #page;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 23, 2015 at 9:32 pm
Thank you guys ..... Thank u so much.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply