[SQL] Is there any sql query that can take only number character in varchar?

i have a column GL_DESCRIPTION which have a value like this

select distinct GL_DESCRIPTION FROM database_actual_pemupukan;

PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - TRANSPORT
PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - UPAH
PEMUPUKAN - CPD HIK ROTASI 2 - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1 - TRANSPORT
PEMUPUKAN - CPD HIK ROTASI 3 - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1 - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - TRANSPORT


i have a new column in INT called Rotasi which can stored value any number character after 'ROTASI'

so for that requirement, my code would be like this :

UPDATE database_actual_pemupukan SET Rotasi = SUBSTRING(GL_DESCRIPTION,
PATINDEX('%[0-9]%', GL_DESCRIPTION), LEN(GL_DESCRIPTION));


and it returns an error like this

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '1 - MATERIAL' to data type int.


also i tried this query

UPDATE database_actual_pemupukan
SET rotasi =
CASE
WHEN PATINDEX('%[0-9]%', GL_DESCRIPTION) > 0 THEN
CAST(
-- Extract the number starting from the first digit
SUBSTRING(GL_DESCRIPTION,
PATINDEX('%[0-9]%', GL_DESCRIPTION),

CASE

WHEN PATINDEX('%[^0-9]%', GL_DESCRIPTION + 'a') = 0 THEN LEN(GL_DESCRIPTION) - PATINDEX('%[0-9]%', GL_DESCRIPTION) + 1
ELSE PATINDEX('%[^0-9]%', GL_DESCRIPTION + 'a') - PATINDEX('%[0-9]%', GL_DESCRIPTION)
END
) AS INT
)
ELSE NULL -- If no number is found, set rotasi to NULL
END;


it returns an error like this

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.


Where my wrong at?

Continue reading...