英文:
Extract data using multiple conditions for the column value?
问题
从某一列中，我需要以下类型的数据：
- 值为6位数且为数字
- "L"后跟5位数字，例如L12345，需要将L更改为0，所以值应为012345
- "L"后跟6位数字，例如L123456，需要将L更改为0，所以值应为0123456
其余的值应该按原样提取。
我有所有条件的单独查询：
条件1：
SELECT [PartNoAsIs]
FROM [NIMS_Eng_Test].[dbo].[Madill_Exraction_Test]
WHERE LEN(PartNoAsIs) = 6 AND IsNumeric(PartNoAsIs) = 1
条件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]'
条件3与条件2几乎相同。
从条件1和条件2中，需要将"L"更改为0，所以值应为012345和012456。然后，必须提取应用这3个条件后剩下的其他值。
运行Microsoft SQL Server标准版版本9.00.2047.00
感谢任何帮助！
英文:
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!
答案1
得分: 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-0][0-0][0-0]' OR
PartNoAsIs LIKE '[A-Za-z][0-9][0-9][0-0][0-0][0-0][0-0]';
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-0][0-0][0-0][0-0]' AND
PartNoAsIs NOT LIKE '[A-Za-z][0-9][0-9][0-0][0-0][0-0][0-0]';
英文:
You may make use of the RIGHT() function here:
<!-- language: sql -->
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:
<!-- language: sql -->
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]';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论