October 9, 2016 at 2:16 am
Hi all,
I have string like this :
DECLARE @input NVARCHAR(max) = '
hihihi
hihihi
hihihi
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ
hihihi
hihihi
hihihi
https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ
hihihi
hihihi
hihihi
hihihi
http://telegram.me/ZOJ20/10236
'
and I want to extract all Urls from this string :
result
--------
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ
https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ
http://telegram.me/ZOJ20/10236
tnx 😀
October 9, 2016 at 4:33 am
Quick suggestion
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @input NVARCHAR(max) = '
hihihi
hihihi
hihihi
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ
hihihi
hihihi
hihihi
https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ
hihihi
hihihi
hihihi
hihihi
http://telegram.me/ZOJ20/10236
';
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(LEN(@input))
ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3,T T4)
,LINE_SET(POS) AS
(
SELECT DISTINCT
CHARINDEX(NCHAR(10),@input,NM.N) AS POS
FROM NUMS NM
)
,START_END_POS(START_POS,STR_LEN) AS
(
SELECT
LS.POS AS START_POS
,LEAD(LS.POS,1,NULL) OVER
(
ORDER BY LS.POS
) - LS.POS AS STR_LEN
FROM LINE_SET LS
)
SELECT
SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) AS URL_STRING
FROM START_END_POS SEP
WHERE SEP.STR_LEN IS NOT NULL
AND SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) LIKE N'[http://][https://]%';
Output
URL_STRING
----------------------------------------------------
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ
October 9, 2016 at 5:03 am
farax_x (10/9/2016)
Hi all,I have string like this :
DECLARE @input NVARCHAR(max) = '
hihihi
hihihi
hihihi
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ
hihihi
hihihi
hihihi
https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ
hihihi
hihihi
hihihi
hihihi
http://telegram.me/ZOJ20/10236
'
and I want to extract all Urls from this string :
result
--------
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ
https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ
http://telegram.me/ZOJ20/10236
tnx 😀
It has no result when url is not located in beginning of the line for example :
DECLARE @input NVARCHAR(max) = N'
test
test https://telegram.me/hiberd_com hihihihihi
hihihihihi
test https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ hihihihihi
hihihihihi
hihihihihi
hihihihihi http://telegram.me/ZOJ20/10236 hihihihihi
';
October 9, 2016 at 5:06 am
Eirikur Eiriksson (10/9/2016)
Quick suggestion😎
USE TEEST;GO
SET NOCOUNT ON;
DECLARE @input NVARCHAR(max) = '
hihihi
hihihi
hihihi
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ
hihihi
hihihi
hihihi
https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ
hihihi
hihihi
hihihi
hihihi
http://telegram.me/ZOJ20/10236
';
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(LEN(@input))
ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3,T T4)
,LINE_SET(POS) AS
(
SELECT DISTINCT
CHARINDEX(NCHAR(10),@input,NM.N) AS POS
FROM NUMS NM
)
,START_END_POS(START_POS,STR_LEN) AS
(
SELECT
LS.POS AS START_POS
,LEAD(LS.POS,1,NULL) OVER
(
ORDER BY LS.POS
) - LS.POS AS STR_LEN
FROM LINE_SET LS
)
SELECT
SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) AS URL_STRING
FROM START_END_POS SEP
WHERE SEP.STR_LEN IS NOT NULL
AND SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) LIKE N'[http://][https://]%';
Output
URL_STRING----------------------------------------------------
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ
It has no result when url is not located in beginning of the line for example :
DECLARE @input NVARCHAR(max) = N'
test
test https://telegram.me/hiberd_com hihihihihi
hihihihihi
test https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ hihihihihi
hihihihihi
hihihihihi
hihihihihi http://telegram.me/ZOJ20/10236 hihihihihi
';
October 9, 2016 at 5:13 am
farax_x (10/9/2016)
It has no result when url is not located in beginning of the line for example :
DECLARE @input NVARCHAR(max) = N'
test
test https://telegram.me/hiberd_com hihihihihi
hihihihihi
test https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ hihihihihi
hihihihihi
hihihihihi
hihihihihi http://telegram.me/ZOJ20/10236 hihihihihi
';
Any other additional requirements you would like to share at this point?
😎
Adding a leading wildcard % in front of the LIKE comparison string will find a url in any position
<snip>
SELECT
SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) AS URL_STRING
FROM START_END_POS SEP
WHERE SEP.STR_LEN IS NOT NULL
AND SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) LIKE N'%[http://][https://]%';
</snip>
October 9, 2016 at 5:29 am
Eirikur Eiriksson (10/9/2016)
farax_x (10/9/2016)
It has no result when url is not located in beginning of the line for example :
DECLARE @input NVARCHAR(max) = N'
test
test https://telegram.me/hiberd_com hihihihihi
hihihihihi
test https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ hihihihihi
hihihihihi
hihihihihi
hihihihihi http://telegram.me/ZOJ20/10236 hihihihihi
';
Any other additional requirements you would like to share at this point?
😎
Adding a leading wildcard % in front of the LIKE comparison string will find a url in any position
<snip>SELECT
SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) AS URL_STRING
FROM START_END_POS SEP
WHERE SEP.STR_LEN IS NOT NULL
AND SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) LIKE N'%[http://][https://]%';
</snip>
the result doesn't contain whole url:
[p]
URL_STRING
-----------------
test https://telegram.me/hiberd_com hihihihihi
test https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ hihihihihi
hihihihihi http://telegram.me/ZOJ20/10236 hihihihihi
[/p]
October 9, 2016 at 7:07 am
farax_x (10/9/2016)
the result doesn't contain whole url:[p]
URL_STRING
-----------------
test https://telegram.me/hiberd_com hihihihihi
test https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ hihihihihi
hihihihihi http://telegram.me/ZOJ20/10236 hihihihihi
[/p]
It isn't nice to have one guessing what the requirements are when trying to help you!
😎
This will chop the URL out of the surrounding garbage
USE TEEST;
GO
SET NOCOUNT ON;
--http://www.sqlservercentral.com/Forums/FindPost1824601.aspx
DECLARE @input NVARCHAR(max) = '
hihihi
hihihi
hihihi
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ1 ASDFA
aldf;lkj ;lkj;j https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ2 ASDF
aldf;lkj ;lkj;j https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ3
hihihi
hihihi
hihihi
https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ4
hihihi
hihihi
hihihi
hihihi
http://telegram.me/ZOJ20/102365
';
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(LEN(@input))
ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3,T T4)
,LINE_SET(POS) AS
(
SELECT DISTINCT
CHARINDEX(NCHAR(10),@input,NM.N) AS POS
FROM NUMS NM
)
,START_END_POS(START_POS,STR_LEN) AS
(
SELECT
LS.POS AS START_POS
,LEAD(LS.POS,1,NULL) OVER
(
ORDER BY LS.POS
) - LS.POS AS STR_LEN
FROM LINE_SET LS
)
,TARGET_LINES AS
(
SELECT
SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) AS URL_STRING
FROM START_END_POS SEP
WHERE SEP.STR_LEN IS NOT NULL
AND SUBSTRING(@input,SEP.START_POS + 1,SEP.STR_LEN) LIKE N'%[http://][https://]%'
)
SELECT
RTRIM(SUBSTRING(TL.URL_STRING
,PATINDEX(N'%[http://][https://]%',TL.URL_STRING)
,ISNULL(NULLIF(CHARINDEX(NCHAR(32),TL.URL_STRING,PATINDEX(N'%[http://][https://]%',TL.URL_STRING) + 1 ),0),LEN(TL.URL_STRING))
- (PATINDEX(N'%[http://][https://]%',TL.URL_STRING)) + 1)) AS FINAL_URL
FROM TARGET_LINES TL;
Output
FINAL_URL
-----------------------------------------------------
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ1
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ2
https://telegram.me/joinchat/BW0OCzvs1F6frTkUj2HEAQ3
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply