CODEWITHSUNDEEP
sqlt-sqlpattern-matchingdesign-patternssubstr
Hell.Bent
Hell.Bent

Reputation: 1677

SQL new column with pattern match

I have a column with a url sting that looks like this

http://www.somedomain.edu/rootsite1/something/something/ or http://www.somedomain.edu/sites/rootsite2/something/something

Basically I want to ONLY return the string up to root site (in another column).. root site can be anyting (but /sites), but it will either follow /sites/ or .edu/

so the above two strings would return:

http://www.somedomain.edu/rootsite1
http://www.somedomain.edu/sites/rootsite2

I can't compile the view with CLR, so I don't think Regex is an option.

Thanks for any help.

Upvotes: 0

Views: 426

Answers (2)

Adam Robinson
Adam Robinson

Reputation: 185663

I think you'll do better by splitting up the URL on the client side and saving it as two pieces in the table (one containing the "root" site, the other containing the site-specific path), then putting them back together again on the client side after retrieval.

If you choose to store them in the table as you describe above, you can use CHARINDEX to determine where the .edu or /sites/ occurs in the string, then use SUBSTRING to break it up based on that index.

If you really need to do this, here's an example:

declare @sites table (URL varchar(500))

insert into @sites
values
('http://www.somedomain.edu/rootsite1/something/something/'),
('http://www.somedomain.edu/sites/rootsite2/something/something')

select
    URL,
    SUBSTRING(URL, 1, case when charindex('/sites/', URL) > 0 then 
        charindex('/', URL, charindex('/sites/', URL) + 7) else 
        charindex('/', URL, charindex('.edu/', URL) + 5) end - 1)

from @sites

Upvotes: 1

Richard Banks
Richard Banks

Reputation: 2983

you could use CHARINDEX, LEN and SUBSTRING to do this although im not sure sql is the best place to do it

DECLARE @testStr VARCHAR(255)
SET @testStr = 'http://www.somedomain.edu/rootsite1/something/something/'

PRINT SUBSTRING(@testStr, 0, CHARINDEX('.edu', @testStr))

Not a full solution but should give you a start

Upvotes: 0

Related Questions