extract all Urls from string

• farax_x

  Hall of Fame

  Points: 3579

  More actions

  October 9, 2016 at 2:16 am

  #314102

  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 😀

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  October 9, 2016 at 4:33 am

  #1905550

  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

  https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ

  http://telegram.me/ZOJ20/10236

• farax_x

  Hall of Fame

  Points: 3579

  More actions

  October 9, 2016 at 5:03 am

  #1905552

  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

  ';

• farax_x

  Hall of Fame

  Points: 3579

  More actions

  October 9, 2016 at 5:06 am

  #1905553

  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

  https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ

  http://telegram.me/ZOJ20/10236

  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

  ';

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  October 9, 2016 at 5:13 am

  #1905554

  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>

• farax_x

  Hall of Fame

  Points: 3579

  More actions

  October 9, 2016 at 5:29 am

  #1905556

  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]

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  October 9, 2016 at 7:07 am

  #1905558

  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

  https://telegram.me/joinchat/DigSjj6xZYsI_H2GRjtNQQ4

  http://telegram.me/ZOJ20/102365