CODEWITHSUNDEEP
sqlregexsap-iq
Daria
Daria

Reputation: 861

SQL extract integer value from a long string

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

Upvotes: 0

Views: 775

Answers (1)

SouravA
SouravA

Reputation: 5253

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

Upvotes: 1

Related Questions