0

How can I replace the $SV### value in this string example if I don't know what the numbers following will be? Or even just get the 1234544 into another string variable.

I tried this but it doesn't replace anything: 

      declare @string varchar(100) = 'F4 Obstructed reach beyond.  [$SV1234544" provided.]'
      SELECT REPLACE(@string,PATINDEX('%$SV[0-9+]%',@string),'test')

Thank you!

grc
  • 19
  • 2
  • 2
    Did you mean to match 1 or more digits like `[0-9]+` ? – The fourth bird Feb 03 '20 at 17:42
  • Yes - I tried it both ways - if I put it outside the [0-9] then it replaces it like: "F4 Obstructed reach beyond. [$SV15279test3-" provided.] – grc Feb 03 '20 at 18:06
  • I want it to match $SV1527903 – grc Feb 03 '20 at 18:07
  • If I do this: SELECT PATINDEX('%$SV[0-9]%',@string) It returns 31, but if I put the + in there SELECT PATINDEX('%$SV[0-9]+%',@string) it returns 0. – grc Feb 03 '20 at 18:10
  • 1
    I found a way around it by using SqlRegex.dll : http://igormicev.com/regex-in-sql-server-for-searching-texts/ – grc Feb 03 '20 at 19:34

1 Answers1

0

The below code will work with the expectation that the number will always be prefixed with "$SV". It goes through a series of iterations to locate the numeric value after "$SV" and then replaces the "$SV" and the numeric number with whatever text you want it replaced with. This code uses the substring, patindex, concat, and replace functions.

DECLARE @string varchar(100) = 'F4 Obstructed reach beyond.  [$SV1234544" provided.]'
DECLARE @SubString Varchar(100) = SUBSTRING(@string, PATINDEX('%$SV[0-9]%', @string), LEN(@string))
SELECT REPLACE(@string,CONCAT('$SV',LEFT(SUBSTRING(@SubString, PATINDEX('%[0-9.-]%', @SubString), 8000),PATINDEX('%[^0-9.-]%', SUBSTRING(@SubString, PATINDEX('%[0-9.-]%', @SubString), 8000) + 'X') -1)),'test')
ItalianStallion4215
  • 139
  • 2
  • 11