如何在sql server中拆分 [英] how to split in sql server
本文介绍了如何在sql server中拆分的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何将单词拆分为sql server并将其存储到字符串数组中,否则我们可以在sql 2008中逐一检索其值
解决方案
< pre lang =sql> DECLARE @ FullName VARCHAR ( 100 )
SET @ FullName = ' John Doe'
SELECT SUBSTRING( @ FullName , 1 ,CHARINDEX(' ', @ FullName ) - 1 ) AS [FirstName],
SUBSTRING( @ FullName ,CHARINDEX(' ', @ FullName )+ 1 ,LEN( @ FullName )) AS [LastName]分割函数用于 SPLIT 基于Delimeter的句子。
Delimeter是一个字符串字符,用于标识子字符串限制。
参考: SQL拆分功能 [ ^ ]
查看此链接
如何在sqlserver中拆分字符串 [ ^ ]
谢谢
--RA
您好Prakash,
试试此代码。
SELECT T1.PRIMARY_KEY,nrvalue(' 。',' varchar(50)' ) as TypeId FROM TABLE_1 AS T1
CROSS APPLY ( SELECT cast(' < r>' + replace(T1。[Names],' ,',' < / r> < r>')+ ' < / r>' as xml)) as S(XMLCol)
cross apply S.XMLCol.nodes(' r' ) as n(r)
how to split a word into sql server and store it into string array or else so that we can retreive its value one by one in sql 2008
解决方案
DECLARE @FullName VARCHAR(100) SET @FullName = 'John Doe' SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1) AS [FirstName], SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName)) AS [LastName]
Ref: Split Name[^]
This SQLSplit Functionis use to SPLIT a sentences based on the Delimeter.
Delimeter is a string character used to identify substring limits.
Refer: SQL Split Function[^]
See this link
how to split string in sqlserver[^]
Thanks
--RA
Hi Prakash,
try this code .
SELECT T1.PRIMARY_KEY,n.r.value('.','varchar(50)') as TypeId FROM TABLE_1 AS T1 CROSS APPLY (SELECT cast('<r>'+replace(T1.[Names], ',', '</r><r>')+'</r>' as xml)) as S(XMLCol) cross apply S.XMLCol.nodes('r') as n(r)
这篇关于如何在sql server中拆分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文