Find Multiple Values in a string

_{Tuesday, July 16, 2019 9:22 AM}

Hi,

I have following values in a column. I would like to find right 2 numbers after _ or -  and if there is nothing on the right side i would like to get 00 in a int data type like if it is M_P577 it should return 00 and if it is P_000020_01 it should return 01.

Thanks.

_{Tuesday, July 16, 2019 10:05 AM ✅Answered}

Check this..

create table test(a varchar(30))

insert into test values('P_000020_01')
insert into test values('P_000030_01')
insert into test values('P_000031_07')
insert into test values('P_000027_05')
insert into test values('P_000026_03')
insert into test values('P-000008-03')
insert into test values('P_000028_03')
insert into test values('P_000031_04')
insert into test values('P_000030_01')
insert into test values('M_P696-04')
insert into test values('M_P571')
insert into test values('M_P570-01')
insert into test values('M_P618-01')
insert into test values('M_P577')
insert into test values('P_000020_01')
insert into test values('M_P577')
insert into test values('M_P570-01')


select a, case when charindex('_', a) > 0 and charindex('_', a, charindex('_', a) + 1) > 0 and substring(a, charindex('_', a, charindex('_', a) + 1) + 1, 1) like '[0-9]' then substring(a, charindex('_', a, charindex('_', a) + 1)+1, 2)
               when charindex('_', a) > 0 and charindex('-', a, charindex('_', a) + 1) > 0 and substring(a, charindex('-', a, charindex('_', a) + 1) + 1, 1) like '[0-9]' then substring(a, charindex('-', a, charindex('_', a) + 1)+1, 2)
               when charindex('-', a) > 0 and charindex('_', a, charindex('-', a) + 1) > 0 and substring(a, charindex('_', a, charindex('-', a) + 1) + 1, 1) like '[0-9]' then substring(a, charindex('_', a, charindex('-', a) + 1)+1, 2)
               when charindex('-', a) > 0 and charindex('-', a, charindex('-', a) + 1) > 0 and substring(a, charindex('-', a, charindex('-', a) + 1) + 1, 1) like '[0-9]' then substring(a, charindex('-', a, charindex('-', a) + 1)+1, 2)
               else '00' end from test

_{Tuesday, July 16, 2019 9:33 AM}

What version of SQL Server you using?

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

_{Tuesday, July 16, 2019 10:06 AM}

Hi Thanks for your reply, i am connected to this for database access and using SSMS 2017 

Microsoft SQL Azure (RTM) - 12.0.2000.8   Jul  3 2019 10:02:53   Copyright (C) 2019 Microsoft Corporation 

_{Tuesday, July 16, 2019 10:21 AM}

Take a look at STRING_SPLIT function 

/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Tuesday, July 16, 2019 10:31 AM}

Hi Thank you for your prompt reply, it is working but very slow as I have huge table. Is there any build in function which can be used in SQL Server 2016?

Regards,

_{Tuesday, July 16, 2019 10:33 AM}

In this case consider writing some .NET code to do the job...

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Tuesday, July 16, 2019 1:16 PM | 1 vote}

Hi mhaidder,

Please try the following solution. It is short and simple.

Hopefully, it will perform much faster on the real data set.

DECLARE @tbl TABLE (a VARCHAR(30) NOT NULL);

INSERT INTO @tbl
VALUES('P_000020_01')
    ,('P_000030_01')
    ,('P_000031_07')
    ,('P_000027_05')
    ,('P_000026_03')
    ,('P-000008-03')
    ,('P_000028_03')
    ,('P_000031_04')
    ,('P_000030_01')
    ,('M_P696-04')
    ,('M_P571')
    ,('M_P570-01')
    ,('M_P618-01')
    ,('M_P577')
    ,('P_000020_01')
    ,('M_P577')
    ,('M_P570-01');

;WITH rs AS
(
    SELECT a,
        PARSENAME(REPLACE(REPLACE(a, '-','_'), '_', '.'), 1) AS token
    FROM @tbl
)
SELECT a
    , IIF(LEN(token) = 2, token, '00') AS token
FROM rs;

_{Tuesday, July 16, 2019 7:55 PM}

>> I have following values in a column. I would like to find right 2 numbers after _ or -  and if there is nothing on the right side I would like to get 00 in a INTEGER data type like if it is M_P577 it should return 00 and if it is P_000020_01 it should return 01. <<

For well over 30 years, the netiquette on SQL forums has been to post DDL. But apparently you feel this does not apply to you and you can make everybody else do the work that you're too lazy and rude to do for us. 

CREATE TABLE Foobar
(foo_id VARCHAR() NOT NULL PRIMARY KEY 
   CHECK (foo_id LIKE ??????)
);

INSERT INTO Foobar
VALUES
('P_000020_01'),
('P_000030_01'),
('P_000031_07'),
('P_000027_05'),
('P_000026_03'),
('P-000008-03'),
('P_000028_03'),
('P_000031_04'),
('P_000030_01'),
('M_P696-04'),
('M_P571'),
('M_P570-01'),
('M_P618-01'),
('M_P577');

I would like to know who designed this mess, so you can fire him. Ideally an identifier (I guess this is an identifier) should have a regular expression to validate it. Do you know the difference between digits and integers? An identifier cannot be converted into an integer because it has no numeric value or magnitude. But perhaps most important, a column should be a scaler value that means it has one and only one meaning, and does not hold multiple data elements in it. If each of these badly designed sub strings has meaning, then it ought to be in its own column. You now have a kludge that will let you keep writing really bad SQL with those case expressions

.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL