27,579
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(c1 VARCHAR(10),c2 VARCHAR(10))
GO
INSERT INTO t
SELECT 'a1','b1'
union ALL SELECT 'a1','b2'
union ALL SELECT 'a2','b3'
union ALL SELECT 'a2','b4'
union ALL SELECT 'a2','b5'
union ALL SELECT 'a3','b6'
union ALL SELECT 'a3','b1'
union ALL SELECT 'a4','b2'
union ALL SELECT 'a4','b3'
union ALL SELECT 'a4','b4'
union ALL SELECT 'a4','b5'
union ALL SELECT 'a4','b6'
SELECT dense_rank()OVER(ORDER BY c1) AS newC1,c1,c2 FROM t
/*
newC1 c1 c2
-------------------- ---------- ----------
1 a1 b1
1 a1 b2
2 a2 b3
2 a2 b4
2 a2 b5
3 a3 b6
3 a3 b1
4 a4 b2
4 a4 b3
4 a4 b4
4 a4 b5
4 a4 b6
*/
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([列1] nvarchar(22),[列2] nvarchar(22))
Insert #A
select N'a1',N'b1' union all
select N'a1',N'b2' union all
select N'a2',N'b3' union all
select N'a2',N'b4' union all
select N'a2',N'b5' union all
select N'a3',N'b6' union all
select N'a3',N'b1' union all
select N'a4',N'b2' union all
select N'a4',N'b3' union all
select N'a4',N'b4' union all
select N'a4',N'b5' union all
select N'a4',N'b6'
Go
--测试数据结束
Select dbo.GET_NUMBER2(列1) AS 列1,列2 from #A
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(c1 VARCHAR(10),c2 VARCHAR(10))
GO
INSERT INTO t
SELECT 'a1','b1'
union ALL SELECT 'a1','b2'
union ALL SELECT 'a2','b3'
union ALL SELECT 'a2','b4'
union ALL SELECT 'a2','b5'
union ALL SELECT 'a3','b6'
union ALL SELECT 'a3','b1'
union ALL SELECT 'a4','b2'
union ALL SELECT 'a4','b3'
union ALL SELECT 'a4','b4'
union ALL SELECT 'a4','b5'
union ALL SELECT 'a4','b6'
SELECT SUBSTRING(c1,2,LEN(c1)) AS newC1,c1,c2 FROM t
/*
newC1 c1 c2
---------- ---------- ----------
1 a1 b1
1 a1 b2
2 a2 b3
2 a2 b4
2 a2 b5
3 a3 b6
3 a3 b1
4 a4 b2
4 a4 b3
4 a4 b4
4 a4 b5
4 a4 b6
*/