Extract a part of string with complex pattern SQL

user_14363101 0
Viewed 131

I am working with string as below:

DOT LTB TOL SP BLM 3X(35X**25.1**GR)BWY BNL

And I am trying to extract the float between the last X and G -> 25.1.

In Python it is an easy task using regex expression: **[0-9]+X([0-9\.]+)G**

But I could not find a way to extract the data here in SQL Server:

I tried to write a function that define the position of X and the ) argument like that:

AS
Begin

    Declare @LastX as INT
    Declare @LastG as INT
    Declare @Diff as INT
    Declare @Result as varchar(50)
    Set @LastX = LEN(@Temp) - CAST(CHARINDEX('X', REVERSE(@Temp)) AS Integer) 
    Set @LastG = LEN(@Temp) - CAST(CHARINDEX(')', REVERSE(@Temp)) AS Integer)
    Set @Diff = @LastG - @LastX
    IF @Diff > 0
        Set @Result = SUBSTRING(@Temp, @LastX, @Diff)
    ELSE
        Set @Result = 0
    
    Return @Result
End

My result is 5X25.1G

Can you help me with this?

I'm also open if you have other suggestion to extract this value in SQL.

wildcard function sql-server sql string
1 Answers

If it's always between the last X and last G, you could use a couple of CHARINDEX's as you have tried. I prefer to do this in the FROM and use APPLY to avoid repetition of code and a "nasty" long single expression:

SELECT *,REVERSE(SUBSTRING(R.YourString,CI1.I+1,CI2.I-CI1.I-1))
FROM (VALUES('DOT LTB TOL SP BLM 3X(35X25.1GR)BWY BNL'))V(YourString)
     CROSS APPLY(VALUES(REVERSE(V.YourString)))R(YourString)
     CROSS APPLY(VALUES(NULLIF(CHARINDEX('G',R.YourString),0)))CI1(I)
     CROSS APPLY(VALUES(NULLIF(CHARINDEX('X',R.YourString,CI1.I),0)))CI2(I)

That really long expression would be the following, by the way:

SELECT V.Yourstring,
       REVERSE(SUBSTRING(REVERSE(V.YourString),NULLIF(CHARINDEX('G',REVERSE(V.YourString)),0)+1,NULLIF(CHARINDEX('X',REVERSE(V.YourString),NULLIF(CHARINDEX('G',REVERSE(V.YourString)),0)),0)-NULLIF(CHARINDEX('G',REVERSE(V.YourString)),0)-1))
FROM (VALUES('DOT LTB TOL SP BLM 3X(35X25.1GR)BWY BNL'))V(YourString)

Have fun trying to read that. Hence why the APPLYs.

user_20299830
Related