目標是取兩個破折號之間的數字，我能夠做到這一點，但問題是我需要洗掉回傳值的前導零。如何在不洗掉所有零的情況下合并 LTRIM 函式或其他函式？
樣本：
123-010-456
結果應該是 10
SELECT[Phone],
REPLACE(SUBSTRING([Phone], CHARINDEX('-', [Phone]), CHARINDEX('-', [Phone])),'-','') AS substring
FROM [SalesLT].[Customer]
uj5u.com熱心網友回復：
如果您的資料模式/格式是固定的，那么下面將有所幫助
SELECT RIGHT(PARSENAME(REPLACE('123-010-456','-','.'),2),2)
uj5u.com熱心網友回復：
您可以將以下內容組合到一個查詢中，但為了更好地理解，我將其分解為 3 個步驟。
DECLARE @String nvarchar(20) = '123-010-456'
DECLARE @MiddlePart nvarchar(20)
DECLARE @FirstNonZero int
--Get the middle part
SET @MiddlePart = SUBSTRING(@String,
CHARINDEX('-',@String) 1, --Find the 1st dash
LEN(@String)-CHARINDEX('-',@String)-CHARINDEX('-',Reverse(@String)) --Find length between two dashes
)
--Get the First Non zero number
SET @FirstNonZero = (SELECT MIN(x)
FROM (SELECT CHARINDEX('1',@MiddlePart) as x WHERE CHARINDEX('1',@MiddlePart)>0
UNION
SELECT CHARINDEX('2',@MiddlePart) as x WHERE CHARINDEX('2',@MiddlePart)>0
UNION
SELECT CHARINDEX('3',@MiddlePart) as x WHERE CHARINDEX('3',@MiddlePart)>0
UNION
SELECT CHARINDEX('4',@MiddlePart) as x WHERE CHARINDEX('4',@MiddlePart)>0
UNION
SELECT CHARINDEX('5',@MiddlePart) as x WHERE CHARINDEX('5',@MiddlePart)>0
UNION
SELECT CHARINDEX('6',@MiddlePart) as x WHERE CHARINDEX('6',@MiddlePart)>0
UNION
SELECT CHARINDEX('7',@MiddlePart) as x WHERE CHARINDEX('7',@MiddlePart)>0
UNION
SELECT CHARINDEX('8',@MiddlePart) as x WHERE CHARINDEX('8',@MiddlePart)>0
UNION
SELECT CHARINDEX('9',@MiddlePart) as x WHERE CHARINDEX('9',@MiddlePart)>0
) a)
--Final
Select SUBSTRING(@MiddlePart,@FirstNonZero,LEN(@MiddlePart)-@FirstNonZero 1)
uj5u.com熱心網友回復：
您可以嘗試提取中間部分并將其轉換為 int - 這將洗掉所有前導零，同時保留尾隨零……如果需要，您可以將其轉換回 varchar。舉個例子：
DECLARE @t TABLE(testval nvarchar(40))
INSERT INTO @t VALUES
('123-010-456')
,('1234-1-456789')
,('12-00010-4')
,('1-0007-4')
SELECT *
,SUBSTRING(testval, CHARINDEX('-', testval) 1, CHARINDEX('-', testval, CHARINDEX('-', testval) 1)-CHARINDEX('-', testval)-1)
,CAST(SUBSTRING(testval, CHARINDEX('-', testval) 1, CHARINDEX('-', testval, CHARINDEX('-', testval) 1)-CHARINDEX('-', testval)-1) AS INT)
FROM @t
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/531029.html
下一篇：使用替換從另一列更新列值