How do I extract data within parentheses from a table with different values?-CodePudding

Im trying to extract data within a column that contains IDs and characters that contain IDs within parentheses. It looks somewhat like this (Btw, there will only be one set of parentheses if they happen to exist within a row):

Col1
Mark(1234)
6789
VZ(X678)
ASD(5677)qwe

Ideal Result
1234
6789
X678
5677

This is what I have so far but its returning an error: 'Invalid length parameter passed to the LEFT or SUBSTRING function.'

SELECT DISTINCT col1,
CASE WHEN col1 like '%(%' then
SUBSTRING (col1,
            CHARINDEX('%(%', col1)   1,
            CHARINDEX('%)%', col1) - CHARINDEX('%(%', col1) - 1)
            else col1 end
from MyTable B;

CodePudding user response：

If interested in a helper Table-Valued Function which will support multiple observations. If you don't want the function, it would be a small matter to migrate the logic into the CROSS APPLY

Example

Declare @YourTable Table ([Col1] varchar(50))  Insert Into @YourTable Values 
 ('Mark(1234)')
,('6789')
,('VZ(X678)')
,('ASD(5677)qwe')
 
Select A.* 
      ,NewValue = coalesce(RetVal,Col1)
 from @YourTable A
 Outer Apply [dbo].[tvf-Str-Extract](Col1,'(',')') B

Results

Col1            NewValue
Mark(1234)      1234
6789            6789
VZ(X678)        X678
ASD(5677)qwe    5677

Results

CREATE FUNCTION [dbo].[tvf-Str-Extract-JSON] (@String nvarchar(max),@Delim1 nvarchar(100),@Delim2 nvarchar(100))
Returns Table 
As
Return (  

    Select RetSeq = row_number() over (order by RetSeq)
          ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
    From  (
            Select RetSeq = [Key] 1
                  ,RetVal = trim(Value)
             From  OpenJSON( N'["' replace(string_escape(@String,'json'),@Delim1,'","') N'"]' )

          ) C1
    Where charindex(@Delim2,RetVal)>1

)

EDIT - Sans TVF

Select A.* 
      ,NewValue = coalesce(RetVal,Col1)
 from @YourTable A
 Outer Apply (
                Select RetSeq = row_number() over (order by RetSeq)
                      ,RetVal = left(RetVal,charindex(')',RetVal)-1)
                From  (
                        Select RetSeq = [Key] 1
                              ,RetVal = trim(Value)
                         From  OpenJSON( N'["' replace(string_escape(Col1,'json'),'(','","') N'"]' )

                      ) C1
                Where charindex(')',RetVal)>1
             ) B

CodePudding user response：

@martin Smith Thanks for pointing out the used of the wildcards. I changed my code to this and its doing what I needed it to do! Using the case when expression to look for the parentheses regardless of location so I kept the % wildcard there but took it out in the CHARINDEX as you mentioned:

SELECT DISTINCT col1,
CASE WHEN col1 like '%(%' then
SUBSTRING (col1,
        CHARINDEX('(', col1)   1,
        CHARINDEX(')', col1) - CHARINDEX('(', col1) - 1)
        else col1 end
from MyTable B;