例如：輸入‘asdasdasd 2019年1月’ 輸出：‘201901’
輸入‘2019年11月’，輸出：‘201911’
輸入‘poiisiis201909’ 輸出‘201909’
uj5u.com熱心網友回復：
如果日期是固定輸入在後面，可以使用：Select right('dfdsffdfs201911',6)uj5u.com熱心網友回復：
日期不是固定的位置，我上述的情況都有可能發生uj5u.com熱心網友回復：
----記得結貼
Declare @str Varchar(4000)
with tb(a)as(
select 'asdasdasd 2019年1月' ---在這裡輸入
),tc as(
select a,number,number-ROW_NUMBER() over(order by a,number) num
from tb,master..spt_values
where type='p' and ASCII(SUBSTRING(a,number,1)) between 48 and 57
),td as(
select a b,substring(a,MIN(number),MAX(number)-MIN(number)+1)a,
ROW_NUMBER() over(partition by a order by min(number))num from tc
group by a,num)
Select @str = IsNull(@str,'') + IsNull(a,'') + '/' From td where num<=2
SET @str = REVERSE(@str)
SET @str = CASE WHEN CHARINDEX('/', @str) = 1 THEN STUFF(@str, 1, 1, '') ELSE @str END
SET @str = REVERSE(@str)
Select case when len( @str) <> 7 then replace(@str, '/', '0') else replace(@str, '/', '') end
uj5u.com熱心網友回復：
是只輸出到月份嗎
DECLARE @A VARCHAR(100)
SET @A='asdasdasd 2019年1月'
;WITH CTE
AS
(SELECT *,SUBSTRING(STRING,NUMBER,1) AS SINGLE_NUM
FROM MASTER.DBO.SPT_VALUES A
JOIN (SELECT @A AS STRING) B ON NUMBER<=LEN(STRING)
WHERE TYPE='P' AND NUMBER>0
AND ASCII(SUBSTRING(STRING,NUMBER,1)) BETWEEN 48 AND 59)
SELECT STRING,LEFT(NUM,4)+RIGHT('0'+SUBSTRING(NUM,5,LEN(NUM)),2)
FROM
(SELECT *,(SELECT CAST(SINGLE_NUM AS VARCHAR)+'' FROM CTE ORDER BY NUMBER FOR XML PATH('')) AS NUM
FROM CTE A) AS A
GROUP BY STRING,NUM
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/84984.html
標籤：疑難問題
下一篇：多維陣列和向量