Splitting string based with a delimiter

Question

I want to extract the string after the second _ character and also remove the (C) in a Microsoft SQL Server Query.

The field name is Asset Code

Table name is dbo.DynamicsMOJ Data

Data Examples:

Values for Asset Code

CP04K_54560_633331 (C)
CP04K_54560_641121 (C)
ME030_72159_690940 (C)
HB21M_60966_1181492 (C)
AW000_1144713 (C)

And so forth, all the values in this field are of similar format however some only have a single "_"

I need to get

'633331'
'641121'
'690940'
'1181492'
'1144713'

Sergey Menshov · Accepted Answer · 2019-04-24 03:01:10Z

1

My variants:

SELECT
  txt,
  CHARINDEX('_',REVERSE(txt)), -- there is 0 for four last examples
  --REPLACE(RIGHT(txt,ISNULL(NULLIF(CHARINDEX('_',REVERSE(txt))-1,-1),LEN(txt))),' (C)','')
  -- the next variants is better if we have different count of spaces before '(C)'
  RTRIM(REPLACE(RIGHT(txt,ISNULL(NULLIF(CHARINDEX('_',REVERSE(txt))-1,-1),LEN(txt))),'(C)','')),
  RTRIM(REPLACE(IIF(CHARINDEX('_',txt)=0,txt,RIGHT(txt,CHARINDEX('_',REVERSE(txt))-1)),'(C)',''))
FROM
  (VALUES
    ('CP04K_54560_633331 (C)'),
    ('CP04K_54560_641121 (C)'),
    ('ME030_72159_690940 (C)'),
    ('HB21M_60966_1181492 (C)'),
    ('AW000_1144713 (C)'),
    (NULL),
    (''),
    ('1234567(C)'),
    ('1234567       (C)'),
    ('1234567')
  ) v(txt)

You can check your data using the following query:

SELECT *
FROM
  (VALUES
    ('CP04K_54560_633331 (C)'),
    ('CP04K_54560_641121 (C)'),
    ('ME030_72159_690940 (C)'),
    ('HB21M_60966_1181492 (C)'),
    ('AW000_1144713 (C)'),
    (NULL),
    (''),
    ('1234567(C)'),
    ('1234567       (C)'),
    ('1234567')
  ) v(txt)
WHERE CHARINDEX('_',txt)=0

I think it will get you all the values without '_'.

edited Apr 24, 2019 at 3:01

answered Apr 23, 2019 at 2:56

Sergey Menshov

3,9062 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matthew Over a year ago

I wanted to extract the string in the whole Asset Code field which has over 200,000 lines. I tried changing FROM (VALUES ('CP04K_54560_633331 (C)'), ('CP04K_54560_641121 (C)'), ('ME030_72159_690940 (C)'), ('HB21M_60966_1181492 (C)'), ('AW000_1144713 (C)') ) v(txt) To FROM dbo.DynamicsMOJ Data Is there anything else that I would need to change in the query to get this to work properly?

Sergey Menshov Over a year ago

No, it's enough - SELECT ... FROM dbo.DynamicsMOJ. And to replace txt into your column name.

Matthew Over a year ago

When I enter that SQL Query it comes up with an error "Msg 536, Level 16, State 2, Line 1 Invalid length parameter passed to the RIGHT function."

Sergey Menshov Over a year ago

@Matthew - I've added several examples and modified the query for specific cases.

Collectives™ on Stack Overflow

Splitting string based with a delimiter

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related