none
Find Multiple Values in a string RRS feed

  • Question

  • 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.

    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

    Thanks.

    Tuesday, July 16, 2019 9:22 AM

Answers

  • 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
    • Edited by SQLNeophyte Tuesday, July 16, 2019 10:17 AM
    • Marked as answer by mhaidder Tuesday, July 16, 2019 10:34 AM
    Tuesday, July 16, 2019 10:05 AM

All replies

  • 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 9:33 AM
  • 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
    • Edited by SQLNeophyte Tuesday, July 16, 2019 10:17 AM
    • Marked as answer by mhaidder Tuesday, July 16, 2019 10:34 AM
    Tuesday, July 16, 2019 10:05 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:06 AM
  • Take a look at STRING_SPLIT function 

    https://docs.microsoft.com/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:21 AM
    Answerer
  • 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:31 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 10:33 AM
    Answerer
  • 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 1:16 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

    Tuesday, July 16, 2019 7:55 PM