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!

Answer 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]';