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.

回答1:

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

回答2:

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