‘壹’ sql一个表A有多列数据是字母，需要关联字典表B，将这多列字母转为汉字。
selecta.*,b1.汉字,b2.汉字fromAa
leftjoinBb1ona.字段1=b1.字段
leftjoinbb2ona.字段2=b2.字段
如果有再多字段就后面 left join 往上加
‘贰’ SQL语句 把用户名中文改成拼音字母
看看这个http://dhjdhja.blog.163.com/blog/static/64762009101684639346/ ps：奶奶的贴一个地址链接还不行什么破论坛，内容又发不完 --sql汉字转拼音
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOcreate function [dbo].[fn_GetPinyin](@words nvarchar(2000))
returns varchar(8000)
as
begin
declare @word nchar(1)
declare @pinyin varchar(8000)
declare @i int
declare @words_len int
declare @unicode int
set @i = 1
set @words = ltrim(rtrim(@words))
set @words_len = len(@words)
while (@i <= @words_len) --循环取字符
begin
set @word = substring(@words, @i, 1)
set @unicode = unicode(@word)
set @pinyin = ISNULL(@pinyin+space(1),'')+
(case when unicode(@word) between 19968 and 19968+20901 then
(select top 1 py from (
select 'a' as py,N'厑' as word
union all select 'ai',N'靉'
union all select 'an',N'黯'
union all select 'ang',N'醠'
union all select 'ao',N'骜'
union all select 'ba',N'欛'
union all select '',N'瓸' --韛兡瓸
union all select 'ban',N'瓣'
union all select 'bang',N'镑'
union all select 'bao',N'鑤'
union all select 'bei',N'鐾'
union all select 'ben',N'輽'
union all select 'beng',N'镚'
union all select 'bi',N'鼊'
union all select 'bian',N'变'
union all select 'biao',N'鳔'
union all select 'bie',N'彆'
union all select 'bin',N'鬓'
union all select 'bing',N'靐'
union all select 'bo',N'卜'
union all select 'bu',N'簿'
union all select 'ca',N'囃'
union all select 'cai',N'乲' --縩乲
union all select 'can',N'爘'
union all select 'cang',N'賶'
union all select 'cao',N'鼜'
union all select 'ce',N'簎'
union all select 'cen',N'笒'
union all select 'ceng',N'乽' --硛硳岾猠乽
union all select 'cha',N'诧'
union all select 'chai',N'囆'
union all select 'chan',N'颤'
union all select 'chang',N'韔'
union all select 'chao',N'觘'
union all select 'che',N'爡'
union all select 'chen',N'谶'
union all select 'cheng',N'秤'
union all select 'chi',N'鷘'
union all select 'chong',N'铳'
union all select 'chou',N'殠'
union all select 'chu',N'矗'
union all select 'chuai',N'踹'
union all select 'chuan',N'鶨'
union all select 'chuang',N'怆'
union all select 'chui',N'顀'
union all select 'chun',N'蠢'
union all select 'chuo',N'縒'
union all select 'ci',N'嗭' --赐嗭
union all select 'cong',N'謥'
union all select 'cou',N'辏'
union all select 'cu',N'顣'
union all select 'cuan',N'爨'
union all select 'cui',N'臎'
union all select 'cun',N'籿'
union all select 'cuo',N'错'
union all select 'da',N'橽'
union all select 'dai',N'靆'
union all select 'dan',N'饏'
union all select 'dang',N'闣'
union all select '',N'纛'
union all select 'de',N'的'
union all select 'den',N'扽'
union all select 'deng',N'镫'
union all select 'di',N'䗖'
union all select 'dia',N'嗲'
union all select 'dian',N'驔'
union all select 'diao',N'鑃'
union all select 'die',N'嚸' --眰嚸
union all select 'ding',N'顁'
union all select 'diu',N'铥'
union all select 'dong',N'霘'
union all select 'dou',N'鬭'
union all select '',N'蠹'
union all select 'an',N'叾' --簖叾
union all select 'i',N'譵'
union all select 'n',N'踲'
union all select 'o',N'鵽'
union all select 'e',N'鳄'
union all select 'en',N'摁'
union all select 'eng',N'鞥'
union all select 'er',N'樲'
union all select 'fa',N'发'
union all select 'fan',N'瀪'
union all select 'fang',N'放'
union all select 'fei',N'靅'
union all select 'fen',N'鲼'
union all select 'feng',N'覅'
union all select 'fo',N'梻'
union all select 'fou',N'鴀'
union all select 'fu',N'猤' --鳆猤
union all select 'ga',N'魀'
union all select 'gai',N'瓂'
union all select 'gan',N'灨'
union all select 'gang',N'戆'
union all select 'gao',N'锆'
union all select 'ge',N'獦'
union all select 'gei',N'给'
union all select 'gen',N'搄'
union all select 'geng',N'堩' --亘堩啹喼𠮶
union all select 'gong',N'兣' --熕贑兝兣
union all select 'gou',N'购'
union all select 'gu',N'顾'
union all select 'gua',N'诖'
union all select 'guai',N'恠'
union all select 'guan',N'鱹'
union all select 'guang',N'撗'
union all select 'gui',N'鱥'
union all select 'gun',N'謴'
union all select 'guo',N'腂'
union all select 'ha',N'哈'
union all select 'hai',N'饚'
‘叁’ sql 语句 急！！！！ 数据将英文和数字去掉,只保留汉字的sql语句
1、创建测试表，
create table test_replace_str(value varchar2(200));
4、编写语句，将英文和数字去掉,只保留汉字；
select t.*, regexp_replace(value, '[a-zA-Z0-9]', '') sec
from test_replace_str t;
‘肆’ SQL Server 如何提取汉字首字母
代码如下：
USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getPinYin] (@str varchar(500) = '')
RETURNS varchar(500) AS
BEGIN
Declare @strlen int,
@return varchar(500),
@ii int,
@c char(1),
@chn nchar(1)
--//初始化变量
Declare @pytable table(
chn char(2) COLLATE Chinese_PRC_CS_AS NOT NULL,
py char(1) COLLATE Chinese_PRC_CS_AS NULL,
PRIMARY KEY (chn)
)
insert into @pytable values('吖', 'A')
insert into @pytable values('八', 'B')
insert into @pytable values('嚓', 'C')
insert into @pytable values('咑', 'D')
insert into @pytable values('妸', 'E')
insert into @pytable values('发', 'F')
insert into @pytable values('旮', 'G')
insert into @pytable values('铪', 'H')
insert into @pytable values('丌', 'I')
insert into @pytable values('丌', 'J')
insert into @pytable values('咔', 'K')
insert into @pytable values('垃', 'L')
insert into @pytable values('呒', 'M')
insert into @pytable values('拏', 'N')
insert into @pytable values('噢', 'O')
insert into @pytable values('妑', 'P')
insert into @pytable values('七', 'Q')
insert into @pytable values('呥', 'R')
insert into @pytable values('仨', 'S')
insert into @pytable values('他', 'T')
--insert into @pytable values('屲', 'U')
--insert into @pytable values('屲', 'V')
insert into @pytable values('屲', 'W')
insert into @pytable values('夕', 'X')
insert into @pytable values('丫', 'Y')
insert into @pytable values('帀', 'Z')
select @strlen = len(@str), @return = '', @ii = 0
//循环整个字符串,用拼音的首字母替换汉字
while @ii < @strlen
begin
select @ii = @ii + 1, @chn = substring(@str , @ii, 1)
if @chn > 'z' --//检索输入的字符串中有中文字符
SELECT @c = max(py)
FROM @pytable
where chn <= @chn
else
set @c=@chn
set @return=@return+@c
end
return @return
END
‘伍’ 求用sql语句分离汉字和字母
SELECT DECODE(
INSTR(term_code,' ')
,0
,Regexp_replace(term_code,'[-A-Za-z0-9]','')
,substr(term_code,0,INSTR(term_code,' ')-1)
) 类型
,DECODE(
INSTR(term_code,' ')
,0
,Regexp_replace(term_code,'[^-A-Za-z0-9]','')
,substr(term_code,INSTR(term_code,' ')+1)
) 型号
FROM table_name;
‘陆’ sql 正则表达式 替换字母+数字
我不知道你用的是什么数据库,下面我给你一个Oracle数据库的替换掉(-N+数字)的例子
update[dbo].[Winit库存]set[商品编号]=REGEXP_REPLACE([商品编号],'-Nd+','')
不同的数据库,用的可以用正则表达式的replace函数不一样
SQL Server中我不知道你那里有没有dbo.RegexReplace函数,是不是还要现建这个替换函数
update[dbo].[Winit库存]set[商品编号]=dbo.RegexReplace([商品编号],'-Nd+','',1)
下面是建立RegexReplace函数的sql语句
--如果存在则删除原有函数
IFOBJECT_ID(N'dbo.RegexReplace')ISNOTNULL
DROPFUNCTIONdbo.RegexReplace
GO
--开始创建正则替换函数
CREATEFUNCTIONdbo.RegexReplace
(
@stringVARCHAR(MAX),--被替换的字符串
@patternVARCHAR(255),--替换模板
@replacestrVARCHAR(255),--替换后的字符串
@IgnoreCaseINT=0--0区分大小写1不区分大小写
)
RETURNSVARCHAR(8000)
AS
BEGIN
DECLARE@objRegexINT,@retstrVARCHAR(8000)
--创建对象
EXECsp_OACreate'VBScript.RegExp',@objRegexOUT
--设置属性
EXECsp_OASetProperty@objRegex,'Pattern',@pattern
EXECsp_OASetProperty@objRegex,'IgnoreCase',@IgnoreCase
EXECsp_OASetProperty@objRegex,'Global',1
--执行
EXECsp_OAMethod@objRegex,'Replace',@retstrOUT,@string,@replacestr
--释放
EXECUTEsp_OADestroy@objRegex
RETURN@retstr
END
GO
--保证正常运行的话，需要将OleAutomationProceres选项置为1
EXECsp_configure'showadvancedoptions',1
RECONFIGUREWITHOVERRIDE
EXECsp_configure'OleAutomationProceres',1
RECONFIGUREWITHOVERRIDE