请教姓名拼音拆分输出的问题

封尘-莫若 2019-02-14 04:15:45

我这边想针对姓名匹配出拼音，然后拆分成一个个的字母进行输出。姓名匹配出拼音这一步完成了，就是拼音拆分成一个个的字母输出这个不知道该怎么做了。
比如张三拆分成z h a n g s a n
李四拆分成l i s i
然后我需要这个每个字母再去其他表里匹配数据。

...全文

784 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

封尘-莫若 2019-02-22

打赏
举报

引用 4 楼二月十六的回复:

方法太长发不上来，写了个博客，用到大版贴的另一个大版的博客内容了，稍微改动了一下
https://blog.csdn.net/sinat_28984567/article/details/87283968

结果：
SELECT * FROM dbo.Fn_PinYin('张三')

这个如果想让某列值都转换成这样好像就不可以了呀

xiaojun4490 2019-02-18

打赏
举报

你需要的是百度'sql server 字符串操作'.....相信我。

封尘-莫若 2019-02-18

打赏
举报

引用 8 楼二月十六的回复:

[quote=引用 7 楼 LXYWXWJQ 的回复:]
整数再进行拆分呢，比如说有好几行数值（这几个数值不在同一行），14 15 20 我想拆分成1 4 1 5 2 0

字段名
14
15
20

最后想得到
1 4 1 5 2 0
这个是吗？
[/quote]

对的

封尘-莫若 2019-02-16

打赏
举报

引用 9 楼疯狂的疯的回复:

哎，你稍微看下我下的代码都能写出来，你问下边的问题我就知道没看我的回复。

各位版主，要是遇到这种不看回复的，我就慢慢的不想回复了，心挺累的

主要不是很明白大神写的那一段是啥意思
{select SUBSTRING(t1.[Name],n,1)
from
(select @str as [Name]) t1
join dbo.Nums t2 on Len(t1.[Name])>=t2.n}

封尘-莫若 2019-02-16

打赏
举报

我这边写的一直报错，后来给删了我也忘了

封尘-莫若 2019-02-16

打赏
举报

也尝试用你的代码来解决，主要是我这边一直报错

，所以就又问了一下

疯狂的疯 2019-02-15

打赏
举报

哎，你稍微看下我下的代码都能写出来，你问下边的问题我就知道没看我的回复。各位版主，要是遇到这种不看回复的，我就慢慢的不想回复了，心挺累的

二月十六版主 2019-02-14

打赏
举报

引用 7 楼 LXYWXWJQ 的回复:

整数再进行拆分呢，比如说有好几行数值（这几个数值不在同一行），14 15 20 我想拆分成1 4 1 5 2 0

字段名 14 15 20 最后想得到 1 4 1 5 2 0 这个是吗？

封尘-莫若 2019-02-14

打赏
举报

整数再进行拆分呢，比如说有好几行数值（这几个数值不在同一行），14 15 20 我想拆分成1 4 1 5 2 0

封尘-莫若 2019-02-14

打赏
举报

疯狂的疯 2019-02-14

打赏
举报


IF OBJECT_ID('dbo.Nums') IS NOT NULL 
    DROP TABLE dbo.Nums
GO
Create table dbo.Nums(n int)

 INSERT INTO dbo.Nums (n)
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
 FROM master.dbo.spt_values;

declare @str varchar(10)
select @str = 'lisi';

select SUBSTRING(t1.[Name],n,1) 
from 
	(select @str as [Name]) t1 
	join dbo.Nums t2 on Len(t1.[Name])>=t2.n

二月十六版主 2019-02-14

打赏
举报

方法太长发不上来，写了个博客，用到大版贴的另一个大版的博客内容了，稍微改动了一下 https://blog.csdn.net/sinat_28984567/article/details/87283968 结果：

SELECT * FROM dbo.Fn_PinYin('张三')

二月十六版主 2019-02-14

打赏
举报

新建函数：

CREATE FUNCTION [dbo].Fn_PinYin (@str VARCHAR(100))
RETURNS @t TABLE (col NVARCHAR(200))
AS
BEGIN
    declare @re varchar(8000),@crs varchar(10)  
 declare @strlen int   
 select @strlen=len(@str),@re=''  
 while @strlen>0  
 begin    
  set @crs= substring(@str,@strlen,1)  
      select @re=  
        CASE   
        when @crs<'吖' COLLATE Chinese_PRC_CS_AS_KS_WS then @crs  
        when @crs<='厑' COLLATE Chinese_PRC_CS_AS_KS_WS then 'A'  
        when @crs<='靉' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ai'  
        when @crs<='黯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'An'  
        when @crs<='醠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ang'  
        when @crs<='驁' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ao'  
        when @crs<='欛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ba'  
        when @crs<='瓸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bai'  
        when @crs<='瓣' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ban'  
        when @crs<='鎊' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bang'  
        when @crs<='鑤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bao'  
        when @crs<='鐾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bei'  
        when @crs<='輽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ben'  
        when @crs<='鏰' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Beng'  
        when @crs<='鼊' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bi'  
        when @crs<='變' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bian'  
        when @crs<='鰾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Biao'  
        when @crs<='彆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bie'  
        when @crs<='鬢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bin'  
        when @crs<='靐' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bing'  
        when @crs<='蔔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bo'  
        when @crs<='簿' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Bu'  
        when @crs<='囃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ca'  
        when @crs<='乲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cai'  
        when @crs<='爘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Can'  
        when @crs<='賶' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cang'  
        when @crs<='鼜' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cao'  
        when @crs<='簎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ce'  
        when @crs<='笒' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cen'  
        when @crs<='乽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ceng'  
        when @crs<='詫' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cha'  
        when @crs<='囆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chai'  
        when @crs<='顫' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chan'  
        when @crs<='韔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chang'  
        when @crs<='觘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chao'  
        when @crs<='爡' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Che'  
        when @crs<='讖' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chen'  
        when @crs<='秤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cheng'  
        when @crs<='鷘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chi'  
        when @crs<='銃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chong'  
        when @crs<='殠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chou'  
        when @crs<='矗' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chu'  
        when @crs<='踹' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chuai'  
        when @crs<='鶨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chuan'  
        when @crs<='愴' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chuang'  
        when @crs<='顀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chui'  
        when @crs<='蠢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chun'  
        when @crs<='縒' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Chuo'  
        when @crs<='嗭' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ci'  
        when @crs<='謥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cong'  
        when @crs<='輳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cou'  
        when @crs<='顣' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cu'  
        when @crs<='爨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cuan'  
        when @crs<='臎' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cui'  
        when @crs<='籿' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cun'  
        when @crs<='錯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Cuo'  
        when @crs<='橽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Da'  
        when @crs<='靆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dai'  
        when @crs<='饏' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dan'  
        when @crs<='闣' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dang'  
        when @crs<='纛' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dao'  
        when @crs<='的' COLLATE Chinese_PRC_CS_AS_KS_WS then 'De'  
        when @crs<='扽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Den'  
        when @crs<='鐙' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Deng'  
        when @crs<='螮' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Di'  
        when @crs<='嗲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dia'  
        when @crs<='驔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dian'  
        when @crs<='鑃' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Diao'  
        when @crs<='嚸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Die'  
        when @crs<='顁' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ding'  
        when @crs<='銩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Diu'  
        when @crs<='霘' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dong'  
        when @crs<='鬭' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dou'  
        when @crs<='蠹' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Du'  
        when @crs<='叾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Duan'  
        when @crs<='譵' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dui'  
        when @crs<='踲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Dun'  
        when @crs<='鵽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Duo'  
        when @crs<='鱷' COLLATE Chinese_PRC_CS_AS_KS_WS then 'E'  
        when @crs<='摁' COLLATE Chinese_PRC_CS_AS_KS_WS then 'En'  
        when @crs<='鞥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Eng'  
        when @crs<='樲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Er'  
        when @crs<='髮' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fa'  
        when @crs<='瀪' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fan'  
        when @crs<='放' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fang'  
        when @crs<='靅' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fei'  
        when @crs<='鱝' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fen'  
        when @crs<='覅' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Feng'  
        when @crs<='梻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fo'  
        when @crs<='鴀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fou'  
        when @crs<='猤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Fu'  
        when @crs<='魀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ga'  
        when @crs<='瓂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gai'  
        when @crs<='灨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gan'  
        when @crs<='戇' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gang'  
        when @crs<='鋯' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gao'  
        when @crs<='獦' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ge'  
        when @crs<='給' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gei'  
        when @crs<='搄' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gen'  
        when @crs<='堩' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Geng'  
        when @crs<='兣' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gong'  
        when @crs<='購' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gou'  
        when @crs<='顧' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gu'  
        when @crs<='詿' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gua'  
        when @crs<='恠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Guai'  
        when @crs<='鱹' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Guan'  
        when @crs<='撗' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Guang'  
        when @crs<='鱥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gui'  
        when @crs<='謴' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Gun'  
        when @crs<='腂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Guo'  
        when @crs<='哈' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ha'  
        when @crs<='饚' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hai'  
        when @crs<='鶾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Han'  
        when @crs<='沆' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hang'  
        when @crs<='兞' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hao'  
        when @crs<='靏' COLLATE Chinese_PRC_CS_AS_KS_WS then 'He'  
        when @crs<='嬒' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hei'  
        when @crs<='恨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hen'  
        when @crs<='堼' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Heng'  
        when @crs<='鬨' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hong'  
        when @crs<='鱟' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hou'  
        when @crs<='鸌' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hu'  
        when @crs<='蘳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hua'  
        when @crs<='蘾' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Huai'  
        when @crs<='鰀' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Huan'  
        when @crs<='鎤' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Huang'  
        when @crs<='顪' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hui'  
        when @crs<='諢' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Hun'  
        when @crs<='夻' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Huo'  
        when @crs<='驥' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ji'  
        when @crs<='嗧' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jia'  
        when @crs<='鑳' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jian'  
        when @crs<='謽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jiang'  
        when @crs<='釂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jiao'  
        when @crs<='繲' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jie'  
        when @crs<='齽' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jin'  
        when @crs<='竸' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jing'  
        when @crs<='蘔' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jiong'  
        when @crs<='欍' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Jiu'  
        when @crs<='爠' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Ju'  
        when @crs<='羂' COLLATE Chinese_PRC_CS_AS_KS_WS then 'Juan'  
        when @crs<='钁' COLLATE Chinese_PRC_CS_AS_K