sql查询或存储过程来中断字符串 [英] sql query or stored procedure to break string

问题描述

亲爱的朋友，

假设我有一个名为employee的表，其表的列为empname.它包含的数据为

1.哈利·波特
2.史蒂夫·约翰


我的要求是

1.哈里
2.potter
3.史蒂夫
4.约翰


谢谢

Dear friends,

Suppose i have a table named as employee having column empname.It contains data as

1.Harry potter
2.Steve john


My requirement is

1.Harry
2.potter
3.Steve
4.john


Thanks

推荐答案

你好朋友，尝试一下...

Hello Friend Try this...

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TestTable') AND type in (N'U'))
DROP TABLE TestTable
GO
CREATE TABLE TestTable(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name Nvarchar(20)
)


INSERT INTO TestTable(Name) VALUES ('Tejas Vaishnav')
INSERT INTO TestTable(Name) VALUES ('Harry Potter')
INSERT INTO TestTable(Name) VALUES ('Steve john')

SELECT * FROM TestTable 

--this will result in this
--1	Tejas Vaishnav
--2	Harry Potter
--3	Steve john

SELECT SUBSTRING(Name, 1,CHARINDEX(' ', Name)) AS FIRSTNAME,SUBSTRING(Name, CHARINDEX(' ', Name),LEN(Name)) AS LASTNAME
FROM TestTable

--this will result in this
--1  Tejas Vaishnav
--2  Harry Potter
--3  Steve john

您可以像查询一样使用它，也可以在存储过程中使用它.

You Can use it like query and also in storeporcedure also.

您是否已选中此项，

http://stackoverflow. com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor [ http://stackoverflow.com/questions/2647/split-string-in-sql [ ^ ]

have you check this,

http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor[^]


http://stackoverflow.com/questions/2647/split-string-in-sql[^]

你好朋友，

试试下面的代码块...

Hi friend,

try this following code block...

WITH Namescte AS (
    SELECT  CAST('<i>' + REPLACE(Name, ' ', '</i><i>') + '</i>' AS XML) AS Names
    FROM YourTable
)
SELECT 
    x.i.value('.', 'VARCHAR(10)') AS Names
FROM Namescte
CROSS APPLY Names.nodes('//i') x(i)

这篇关于sql查询或存储过程来中断字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！