0

I have a table with varchar(300) (it containts some URLs) column, and I need to extract some value of this column to a new integer column.

Source column:

https://my_domain.com/?item1=value1&item2=value2&searching_field=int_value&item4=value4

I need to find string searching_field=int_value and insert int_value to a table.

UPD. I found a good tool REGEXP_SUBSTRING and I'm trying to use like below:

SELECT REGEXP_SUBSTR(col_name, 'searching_field=[0-9]+', charindex('searching_field=', col)) as col from my_table
Daria
  • 861
  • 11
  • 29
  • I guess the issue with your `REGEXP` is that it accounts for only one character. If say you want to substitute 3 characters(numbers), your `REGEXP` should be `'searching_field=[0-9][0-9][0-9]+&'` – SouravA Jan 12 '15 at 21:02
  • Thanks for input but `'searching_field=[0-9]+'` works correctly for me. – Daria Jan 12 '15 at 21:16
  • 1
    [This][1] is the solution. I created it by my own [1]: http://stackoverflow.com/a/28001029/4247360 – Daria Jan 19 '15 at 08:51

1 Answers1

1
DECLARE @occur_start INT;
DECLARE @occur_end INT;

CREATE TABLE tempdb..#Temp
(col varchar(300), IntVal int)

INSERT INTO tempdb..#Temp
select col, SUBSTRING(col, CHARINDEX('searching_field=', col), CHARINDEX('&item4=value4', col) - CHARINDEX('searching_field=', col)-1) IntValue
FROM YourTable

INSERT INTO YourOtherTable
SELECT IntValue FROM #Temp
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • Thanks a lot... but I don't know the real values that go after `searching_field=int_value`... so it could be not `item4=value4`, it could be something else – Daria Jan 12 '15 at 19:19
  • Okay... let's asume that max length of my value is 10. Have to check it/ – Daria Jan 12 '15 at 19:32