Extract data using multiple conditions for the column value?

Question

From a certain column I need the following type of data:

Value is 6 characters long and is numerical
“L” is any letter followed by 5 numerical characters, for example L12345 the L needs to be turned into a 0 so the value should be 012345
“L” is any letter followed by 6 numerical characters, for example L123456 the L needs to be turned into a 0 so the value should be 0123456

The rest of the values are to be extracted as is.

I have all the conditions in sperate queries: Condition 1:

SELECT [PartNoAsIs]
FROM [NIMS_Eng_Test].[dbo].[Madill_Exraction_Test]
Where  LEN(PartNoAsIs) = 6 AND IsNumeric(PartNoAsIs)=1

Condition 2:

SELECT [PartNoAsIs]
FROM [NIMS_Eng_Test].[dbo].[Madill_Exraction_Test]
WHERE [PartNoAsIs] LIKE '[A-Za-z][0-9][0-9][0-9][0-9][0-9]'

Condition 3 is almost same as Condition 2

From Condition 1 and 2 L needs to be turned into a 0 so the value should be 012345 and 012456 respectively. Then rest of the values that are left after applying these 3 conditions have to extracted.

Picture of a sample output is attached

Running Microsoft SQL Server Standard Edition Version 9.00.2047.00 Any help is appreciated!

Tim Biegeleisen · Accepted Answer · 2023-06-10 05:59:58Z

1

You may make use of the RIGHT() function here:

SELECT PartNoAsIs, '0' + RIGHT(PartNoAsIs, LEN(PartNoAsIs) - 1) AS PartOut
FROM [NIMS_Eng_Test].[dbo].[Madill_Exraction_Test]
WHERE PartNoAsIs LIKE '[A-Za-z][0-9][0-9][0-9][0-9][0-9]' OR
      PartNoAsIs LIKE '[A-Za-z][0-9][0-9][0-9][0-9][0-9][0-9]';

For the rest of the data use:

SELECT *
FROM [NIMS_Eng_Test].[dbo].[Madill_Exraction_Test]
WHERE PartNoAsIs NOT LIKE '[A-Za-z][0-9][0-9][0-9][0-9][0-9]' AND
      PartNoAsIs NOT LIKE '[A-Za-z][0-9][0-9][0-9][0-9][0-9][0-9]';

edited Jun 10, 2023 at 5:59

answered Jun 9, 2023 at 1:29

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

3 Comments

Brute Over a year ago

Thanks for answering! The query works so that is great. However, I also need the data that is left out by the query. I have around 12000 entries, this query provides 1400 entries. How would I get rest of 10600 rows?

Tim Biegeleisen Over a year ago

You should add sample data showing how you want to view this other non matching data.

Brute Over a year ago

added the picture of sample output.

Collectives™ on Stack Overflow

Extract data using multiple conditions for the column value?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related