有表A
ID NAME
1 A
2 B
3 C
表B
VALUE
A/B/C
A/B
B/C
A/C
A
B
C
怎么样执行一个函数或者其他方法把结果变成
VALUE
1/2/3
1/2
2/3
1/3
1
2
3
谢谢
ID NAME
1 A
2 B
3 C
表B
VALUE
A/B/C
A/B
B/C
A/C
A
B
C
怎么样执行一个函数或者其他方法把结果变成
VALUE
1/2/3
1/2
2/3
1/3
1
2
3
谢谢
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([ID] [int],[NAME] [nvarchar](10))
INSERT INTO [A]
SELECT '1','A' UNION ALL
SELECT '2','B' UNION ALL
SELECT '3','C'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([VALUE] [nvarchar](10))
INSERT INTO [B]
SELECT 'A/B/C' UNION ALL
SELECT 'A/B' UNION ALL
SELECT 'B/C' UNION ALL
SELECT 'A/C' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'--SELECT * FROM [A]
--SELECT * FROM [B]-->SQL查询如下:
IF OBJECT_ID('f_getstr')>0
DROP FUNCTION f_getstr
GO
CREATE FUNCTION f_getstr
(
@s VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
SELECT @s = REPLACE('/'+@s+'/','/'+[NAME]+'/','/'+LTRIM([ID])+'/'),
@s= REPLACE(@s,'//','/')
FROM A
RETURN LEFT(STUFF(@s, 1, 1, ''), LEN(@s)-2)
END
GOSELECT dbo.f_getstr([VALUE]) result FROM B
/*
result
--------------------
1/2/3
1/2
2/3
1/3
1
2
3(7 行受影响)
*/
DROP TABLE [A]
GO
CREATE TABLE [A] ([ID] [int],[NAME] [nvarchar](10))
INSERT INTO [A]
SELECT '1','A' UNION ALL
SELECT '2','B' UNION ALL
SELECT '3','C'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([VALUE] [nvarchar](10))
INSERT INTO [B]
SELECT 'A/B/C' UNION ALL
SELECT 'A/B' UNION ALL
SELECT 'B/C' UNION ALL
SELECT 'A/C' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
--select * from b--select * from acreate function getString(@s varchar(200))
returns varchar(200)
as
begin
select @s=replace(@s,[name],id) from a
return @s
endselect dbo.getString([VALUE]) from b