I have a string that contains some text:

Example:

XPTOP XPTOP 4WS00632 BLACK VERNIS

I want to extract only

4WS00632

and ignore everything else.

I did try indexing the spaces, using charindex for searching all the spaces and then a substring to start at x, end at y.

But, no luck. Sometimes it returns "4WS0063" or "P 4WS00632".
This data is not coherent only "XPTOP XPTOP" is somewhat coherent.
For instance "4WS00632" this has 8 digits, but it might have 9 or 12.

So, I really need to catch everything in between the 3rd space and 4th space.

回答

Select A.SomeCol
      ,NewVal = JSON_VALUE('["'+replace(SomeCol,' ','","')+'"]','$[2]')
 From YourTable  A

SomeCol                                 NewVal
XPTOP XPTOP 4WS00632 BLACK VERNIS       4WS00632