I inherited a table with identifiers in a format [nonnumericprefix][number]. For example (ABC123; R2D2456778; etc). I was wondering if there was a good way to split this in SQL into two fields, the largest integer formed from the right side, and the prefix, for example (ABC, 123; R2D, 2456778; etc). I know I can do this with a cursor, C# code, etc - and I will if I have to - but I don't run into things I cannot do fast and easily in SQL very often, so I thought I'd post it here.
我以[nonnumericprefix] [number]格式继承了一个带有标识符的表。例如(ABC123; R2D2456778;等)。我想知道是否有一种很好的方法可以将SQL分成两个字段,例如右侧形成的最大整数和前缀,例如(ABC,123; R2D,2456778;等)。我知道我可以使用游标,C#代码等来实现这一点 - 如果必须的话,我会这样做 - 但我不会遇到我在SQL中不能快速轻松地做的事情,所以我想我会发布它这里。
3 个解决方案
#1
13
- Reverse the string
- Use PATINDEX to find the first occurrence of a non numeric field
- Use the LEFT function to return the numeric portion of the string
反转字符串
使用PATINDEX查找非数字字段的第一个匹配项
使用LEFT函数返回字符串的数字部分
Code sample
DECLARE @myString varchar(100);
DECLARE @largestInt int;
SET @myString = 'R2D2456778'
SET @mystring = REVERSE(@myString);
SET @largestInt = LEFT(@myString, PATINDEX('%[a-z]%', @myString) - 1)
PRINT ( CONVERT(varchar(100), @largestInt) )
#2
12
You can use PATINDEX
with a pattern like '%[^0123456789]%'
or '%[^0-9]%'
to find the position of the first non-numeric character
你可以使用PATINDEX和'%[^ 0123456789]%'或'%[^ 0-9]%'这样的模式来查找第一个非数字字符的位置
#3
7
You could try something like
你可以试试像
DECLARE @Table TABLE(
Val VARCHAR(50)
)
INSERT INTO @Table SELECT 'ABC123'
INSERT INTO @Table SELECT 'R2D2456778'
SELECT *,
LEFT(Val,LEN(Val) - (PATINDEX('%[^0-9]%',REVERSE(Val)) - 1)),
RIGHT(Val,(PATINDEX('%[^0-9]%',REVERSE(Val)) - 1))
FROM @Table
#1
13
- Reverse the string
- Use PATINDEX to find the first occurrence of a non numeric field
- Use the LEFT function to return the numeric portion of the string
反转字符串
使用PATINDEX查找非数字字段的第一个匹配项
使用LEFT函数返回字符串的数字部分
Code sample
DECLARE @myString varchar(100);
DECLARE @largestInt int;
SET @myString = 'R2D2456778'
SET @mystring = REVERSE(@myString);
SET @largestInt = LEFT(@myString, PATINDEX('%[a-z]%', @myString) - 1)
PRINT ( CONVERT(varchar(100), @largestInt) )
#2
12
You can use PATINDEX
with a pattern like '%[^0123456789]%'
or '%[^0-9]%'
to find the position of the first non-numeric character
你可以使用PATINDEX和'%[^ 0123456789]%'或'%[^ 0-9]%'这样的模式来查找第一个非数字字符的位置
#3
7
You could try something like
你可以试试像
DECLARE @Table TABLE(
Val VARCHAR(50)
)
INSERT INTO @Table SELECT 'ABC123'
INSERT INTO @Table SELECT 'R2D2456778'
SELECT *,
LEFT(Val,LEN(Val) - (PATINDEX('%[^0-9]%',REVERSE(Val)) - 1)),
RIGHT(Val,(PATINDEX('%[^0-9]%',REVERSE(Val)) - 1))
FROM @Table