How do I combine a substring and trim right in SQL-CodePudding

I am trying to extract the data between two underscore characters. In some situations, the 2nd underscore may not exist.

MyFld
P_36840
U_216137
C_203134_H
C_203134_W

I tried this:

substring(i.[MyFld],
      CHARINDEX ('_',i.[MyFld]) 1,len(i.[MyFld])
     -CHARINDEX ('_',i.[MyFld])
) [DerivedPrimaryKey]

And I get this:

DerivedPrimaryKey
36840
216137
203134_H
203134_W

https://dbfiddle.uk/uPKC6oX4

I want to remove the second underscore and data that follows it. I'm trying to combine it with a trim right, but I'm unsure where to start.

How can I do this?

CodePudding user response：

Just a two more options. One using parsename() provided you don't have more than 4 segments. The second using a JSON array

Example

Declare @YourTable Table ([MyFld] varchar(50))  Insert Into @YourTable Values 
 ('P_36840')
,('U_216137')
,('C_203134_H')
,('C_203134_W')
 
Select *
      ,UsingParseName = reverse(parsename(reverse(replace(MyFld,'_','.')),2))
      ,UsingJSONValue = json_value('["' replace(MyFld,'_','","') '"]','$[1]')
 From  @You

Results

MyFld       UsingParseName  UsingJSONValue
P_36840     36840           36840
U_216137    216137          216137
C_203134_H  203134          203134
C_203134_W  203134          203134

CodePudding user response：

We can start by simplifying what you have so far. I will also add enough to make this a complete query, so we can see it in context for later steps:

SELECT 
   right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)) [DerivedPrimaryKey]
FROM I

With this much done, we can now use it as the source for removing the trailing portion of the field:

SELECT 
   reverse(substring(reverse(step1)
      , charindex('_', reverse(step1)) 1
      , len(step1)
  )) [DerivedPrimaryKey]
FROM (
    SELECT right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)) [step1]
    FROM I
) T

Notice the layer of nesting. You can, of course, remove the nesting, but it means replicating the entire inner expression every time you see step1 (good thing I took the time to simplify it):

SELECT 
   reverse(substring(reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
      , charindex('_', reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))) 1
      , len(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
  ))
FROM I

And now back to just the expression:

reverse(substring(reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
      , charindex('_', reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))) 1
      , len(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
  ))

See it work here:

https://dbfiddle.uk/nFO4Vwhm

There is also this alternate expression that saves one function call:

left(   right(i.MyFld,len(i.MyFld)-charindex('_',i.MyFld)), 
   coalesce(
      nullif(
        charindex('_', 
            right(i.MyFld,len(i.MyFld)-charindex('_',i.MyFld)) 
         ) -1, -1,
      ), 
      len( right(i.MyFld,len(i.MyFld)-charindex('_',i.MyFld)) )
    )
)

CodePudding user response：

Often I try to fake out SQL if an expected character isn't always present and I don't need the resulting value:

SELECT SUBSTRING(field_Calculated, 1, CHARINDEX('_', field_Calculated) - 1)  
FROM (SELECT SUBSTRING(MyFld, CHARINDEX('_', MyFld)   1, LEN(MyFld))   '_' As field_Calculated 
  FROM MyTable) T

I think this is clear, but I really like the ParseName solution @JohnCappalletti suggests.

CodePudding user response：

your data

Declare @Table Table ([MyFld] varchar(100))  
Insert Into @Table
([MyFld] ) Values 
('P_36840')
,('U_216137')
,('C_203134_H')
,('C_203134_W')

use SubString,Left and PatIndex

select 
  Left(
    SubString(
      [MyFld], 
      PatIndex('%[0-9.-]%', [MyFld]), 
      8000
    ), 
    PatIndex(
      '%[^0-9.-]%', 
      SubString(
        [MyFld], 
        PatIndex('%[0-9.-]%', [MyFld]), 
        8000
      )   'X'
    )-1
  )  as DerivedPrimaryKey
from 
  @Table

CodePudding user response：

We can do this:

Declare @testData Table ([MyFld] varchar(50));
 Insert Into @testData (MyFld)
 Values ('P_36840')
      , ('U_216137')
      , ('C_203134_H')
      , ('C_203134_W');
 
 Select *
      , second_element = substring(v.MyFld, p1.pos, p2.pos - p1.pos - 1)
   From @testData                                               As td
  Cross Apply (Values (concat(td.MyFld, '__')))                 As v(MyFld)  -- Make sure we have at least 2 delimiters
  Cross Apply (Values (charindex('_', v.MyFld, 1)   1))         As p1(pos)   -- First Position
  Cross Apply (Values (charindex('_', v.MyFld, p1.pos)   1))    As p2(pos)   -- Second Position

If you actually have a fixed number of characters in the first element, then it could be simplified to:

 Select *
      , second_element = substring(v.MyFld, 3, charindex('_', v.MyFld, 4) - 3)
   From @testData td
  Cross Apply (Values (concat(td.MyFld, '_'))) As v(MyFld)