SQL Server Extract data using multiple conditions for the column value?

q3aa0525 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(120)

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!

sql-server

来源：https://stackoverflow.com/questions/76436627/extract-data-using-multiple-conditions-for-the-column-value

1条答案

按热度按时间

nlejzf6q1#

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

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Extract data using multiple conditions for the column value?

1条答案

相关问题

热门标签

最新问答