0

I have rows like below

10470_AMK-4799__OFS_BROWSERTC
6038_DMUSER_DM.READ.REPLACEMENT
6043_DMUSER2__OFS_DM.OFS.SRC.VAL
10194_AMK-TC037__OFS_BROWSERTC
64948_AMK-TABLETUSER__OFS_TWS
10010_AMK-0357

I want to write a query which can produce the part of string like below (from the first underscore up to the second, both not included):

AMK-4799
DMUSER 
DMUSER2
AMK-TC037
AMK-TABLETUSER
AMK-0357

I am using Microsoft SQL Server 2014.

0

2 Answers 2

2

Assuming you want data between first instance of _ and second instance of _ OR length of string in-case second _ is not present.

Try this Query:

-- Create Table Variable
DECLARE @tblString TABLE(StringVal VARCHAR(512));
-- Insert Values in Table Variable
INSERT INTO @tblString(StringVal)
select '10470_AMK-4799__OFS_BROWSERTC' UNION ALL
select '6038_DMUSER_DM.READ.REPLACEMENT' UNION ALL
select '6043_DMUSER2__OFS_DM.OFS.SRC.VAL' UNION ALL
select '10194_AMK-TC037__OFS_BROWSERTC' UNION ALL
select '64948_AMK-TABLETUSER__OFS_TWS' UNION ALL
select '10010_AMK-0357';
-- Select and process for expected Result
SELECT SUBSTRING(
                  SUBSTRING(StringVal,CHARINDEX('_',StringVal,0)+1,LEN(StringVal))
                ,0
                ,CASE when CHARINDEX('_',SUBSTRING(StringVal,CHARINDEX('_',StringVal,0)+1,LEN(StringVal))) <> 0
                    then CHARINDEX('_',SUBSTRING(StringVal,CHARINDEX('_',StringVal,0)+1,LEN(StringVal)))
                    else LEN(StringVal) as Result
                end
                )
FROM @tblString

The results are as below:

Result
-------
AMK-4799
DMUSER
DMUSER2
AMK-TC037
AMK-TABLETUSER
AMK-0357

This will achieve current scope.

0
DECLARE @table TABLE(id_name VARCHAR(1000))

INSERT INTO @table
(
      id_name
)
select '10470_AMK-4799__OFS_BROWSERTC' UNION ALL
select '6038_DMUSER_DM.READ.REPLACEMENT' UNION ALL
select '6043_DMUSER2__OFS_DM.OFS.SRC.VAL' UNION ALL
select '10194_AMK-TC037__OFS_BROWSERTC' UNION ALL
select '64948_AMK-TABLETUSER__OFS_TWS'

SELECT SUBSTRING(
                  SUBSTRING(id_name,CHARINDEX('_',id_name,0)+1,LEN(id_name)),0,CHARINDEX('_',SUBSTRING(id_name,CHARINDEX('_',id_name,0)+1,LEN(id_name)))) 
FROM @table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.