27,579
社区成员
发帖
与我相关
我的任务
分享
SET NOCOUNT ON
GO
USE TEMPDB
GO
IF Object_id('t_dic') IS NOT NULL
DROP TABLE t_dic
GO
CREATE TABLE t_dic
(
a VARCHAR(MAX),
b VARCHAR(MAX)
)
INSERT INTO t_dic
SELECT '中',
'Middle'
UNION ALL
SELECT '中国',
'China'
UNION ALL
SELECT '中国人',
'Chinese'
UNION ALL
SELECT '爱',
'Love'
UNION ALL
SELECT '国',
'country'
UNION ALL
SELECT '在',
'In'
UNION ALL
SELECT '家',
'Home'
GO
DECLARE @STR VARCHAR(MAX),
@I INT,
@STR_RESULT VARCHAR(MAX),
@STR_TMP VARCHAR(MAX)
SELECT @STR = '中国人傻爱中国傻家中国'
WHILE Len(@STR) > 1
BEGIN
SET @STR_TMP = Substring(@STR, 1, 1)
SELECT TOP 1 @STR_RESULT = Isnull(@STR_RESULT + '_', '') + b,
@I = Len(a)
FROM t_dic
WHERE @STR LIKE a + '%'
ORDER BY Len(a) DESC
IF @@ROWCOUNT = 0
BEGIN
SET @I = 1
SET @STR_RESULT = @STR_RESULT + '_' + @STR_TMP
END
SET @STR = Stuff(@STR, 1, @I, '')
END
SELECT @STR_RESULT
/**********************************
Chinese_傻_Love_China_傻_Home_China
**********************************/
SET NOCOUNT ON
GO
USE TEMPDB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
COL1 VARCHAR(MAX)
,COL2 VARCHAR(MAX)
)
INSERT INTO TB
select '中','Middle' union all
select '中国','China' union all
select '中国人','Chinese' union all
select '爱','Love' union all
select '国','country' union all
select '在','In' union all
select '家','Home'
GO
DECLARE @STR VARCHAR(MAX),@I INT ,@STR_RESULT VARCHAR(MAX)
SELECT @STR='中国人傻爱中国傻家中国',@STR_RESULT=''
,@I=1
WHILE @I<=LEN(@STR)
BEGIN
IF EXISTS(SELECT 1 FROM TB WHERE COL1 LIKE SUBSTRING(@STR,@I,1)+'%' AND @I+LEN(COL1)<=LEN(@STR)+1 AND STUFF(@STR,1,@I-1,'') LIKE COL1+'%')
BEGIN
SELECT TOP 1 @STR_RESULT=@STR_RESULT+COL2+'_',@I=@I+LEN(COL1)
FROM TB
WHERE COL1 LIKE SUBSTRING(@STR,@I,1)+'%' AND @I+LEN(COL1)<=LEN(@STR)+1 AND STUFF(@STR,1,@I-1,'') LIKE COL1+'%'
ORDER BY LEN(COL1) DESC
END
ELSE
BEGIN
SELECT @STR_RESULT=@STR_RESULT+SUBSTRING(@STR,@I,1)
SET @I=@I+1
END
END
IF @STR_RESULT LIKE '%[_]' SET @STR_RESULT=LEFT(@STR_RESULT,LEN(@STR_RESULT)-1)
SELECT @STR_RESULT
--Chinese_傻Love_China_傻Home_China