使用sql查询删除单词之间的空格 [英] Removing the spaces between words using sql query
问题描述
我通过在获取的列中添加2列作为单列bt来从数据库中获取数据,这些单词之间有很多空格,以便如何删除空格除了一个空格,
这是我的查询的样子:
选择 Pers_FirstName + Pers_LastName as 名称来自人 inner join PhoneLink on Pers_PersonId = PLink_RecordID 内部 加入电话 Phon_PhoneId = PLink_PhoneId 和 Phon_Number = ' 6983000';
< br $>
输出:
名称
彼得杰克逊
提前谢谢。
编辑:错字
要添加到King Fisher的答案,如果多个空格位于单个文本列中和/或您希望保留单个空格,则使用 REPLACE [ ^ ]功能。请考虑以下示例
SELECT REPLACE(' A B',' ',' ');
SELECT REPLACE(' A B',' ',' ');;
问题是如果你有两个以上的空格,那么REPLACE将取代双空格只有一次。为此,您可以构建一个小型UDF,也可以根据需要多次重复REPLACE。例如
SELECT REPLACE(REPLACE('' A B',' ',' '),' ',' '),' ',' ');
用户TRIM
删除空格的功能
TRIM [ ^ ]
RTEIM [ ^ ]
LTRIM [ ^ ]
解决方案2由 Mika Wendelius [ ^ ]非常好。我建议使用 CTE [ ^ ]:
DECLARE @ tmp TABLE (MyText VARCHAR ( 500 ))
INSERT INTO @ tmp ( MyText)
VALUES (' 这是非常的奇怪的文字......'),
(' 这是另一个非常奇怪的文字。' ),
(' 这是非常愚蠢的文字!!!' )
DECLARE @ words TABLE (RowNo INT ,PartNo INT ,MyWord VARCHAR ( 255 ))
; WITH CTE AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY MyText) AS RowNo, 1 AS PartNo, LEFT (LTRIM(MyText),CHARINDEX(' ',LTRIM(MyText) )-1) AS MyWord, RIGHT (LTRIM(MyText),LEN(LTRIM(MyText)) ) - CHARINDEX(' ',LTRIM(MyText))) AS 剩余
FROM @ tmp
WHERE CHARINDEX(' ',LTRIM(MyText))> ; 0
UNION ALL
SELECT RowNo,PartNo + 1 AS PartNo, LEFT (LTRIM(剩余),CHARINDEX(' ',LTRIM(剩余)) -1) AS MyWord, RIGHT (LTRIM(剩余),LEN(LTRIM(剩余)) - CHARINDEX (' ',LTRIM(剩余))) AS 剩余
FROM CTE
WHERE CHARINDEX(' ',LTRIM(剩余))> 0
UNION ALL
SELECT RowNo,PartNo + 1 AS PartNo,LTRIM(剩余) AS MyWord, NULL AS 剩余
FROM CTE
WHERE CHARINDEX(' ',LTRIM(剩余))= 0
)
INSERT INTO @ words (RowNo,PartNo,MyWord)
SELECT RowNo,PartNo,RTRIM(LTRIM(MyWord)) AS MyWord
FROM CTE
ORDER BY RowNo,PartNo
< span class =code-comment> - SELECT *
- FROM @words
- ORDER BY RowNo,PartNo
SELECT DISTINCT t2.RowNo,
(
SELECT t1.MyWord + ' ' AS [ text ()]
来自 @ words A S t1
其中 t1.RowNo = t2.RowNo
ORDER BY t1.PartNo
对于 XML PATH(' ')
) AS MySentence
来自 @ words AS t2
结果:
RowNo MySentence
1这是非常奇怪的文字...
2这是非常愚蠢的文字!
3这是另一个非常奇怪的文字。
hi,
i'm fetching the data from the database by adding the 2 columns as a single column bt in the fetched column there are lot of spaces between the words so how to remove the spaces except a single space,
here is how my query look like:
select Pers_FirstName + Pers_LastName as Name from Person inner join PhoneLink on Pers_PersonId=PLink_RecordID inner join Phone on Phon_PhoneId=PLink_PhoneId and Phon_Number='6983000';
output:
Name
Peter Jackson
Thank you in advance.
Edited:typo
To add to King Fisher's answer, if the multiple spaces are inside a single text column and/or you want a single space to remain then use REPLACE[^] function. Consider the following examples
SELECT REPLACE('A B' , ' ', ' '); SELECT REPLACE('A B' , ' ', ' ');;
The problem is that if you have more than two spaces then REPLACE would replace dual spaces only once. For this you can either build a small UDF or you can repeat the REPLACE as many times as needed. For example
SELECT REPLACE( REPLACE( REPLACE('A B', ' ', ' '), ' ', ' '), ' ', ' ');
UserTRIM
Function to remove spaces
TRIM[^]
RTEIM[^]
LTRIM[^]
Solution 2 provided by Mika Wendelius[^] is very good. I'd propose to use CTE[^]:
DECLARE @tmp TABLE(MyText VARCHAR(500)) INSERT INTO @tmp (MyText) VALUES('This is very strange text...'), ('This is another very strange text.'), ('This is very stupid text !!!') DECLARE @words TABLE(RowNo INT, PartNo INT, MyWord VARCHAR(255)) ;WITH CTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY MyText) AS RowNo, 1 AS PartNo, LEFT(LTRIM(MyText), CHARINDEX(' ', LTRIM(MyText))-1) AS MyWord, RIGHT(LTRIM(MyText), LEN(LTRIM(MyText)) - CHARINDEX(' ', LTRIM(MyText))) AS Remainder FROM @tmp WHERE CHARINDEX(' ', LTRIM(MyText))>0 UNION ALL SELECT RowNo, PartNo + 1 AS PartNo, LEFT(LTRIM(Remainder), CHARINDEX(' ', LTRIM(Remainder))-1) AS MyWord, RIGHT(LTRIM(Remainder), LEN(LTRIM(Remainder)) - CHARINDEX(' ', LTRIM(Remainder))) AS Remainder FROM CTE WHERE CHARINDEX(' ', LTRIM(Remainder))>0 UNION ALL SELECT RowNo, PartNo + 1 AS PartNo, LTRIM(Remainder) AS MyWord, NULL AS Remainder FROM CTE WHERE CHARINDEX(' ', LTRIM(Remainder))=0 ) INSERT INTO @words (RowNo, PartNo, MyWord) SELECT RowNo, PartNo, RTRIM(LTRIM(MyWord)) AS MyWord FROM CTE ORDER BY RowNo, PartNo --SELECT * --FROM @words --ORDER BY RowNo, PartNo SELECT DISTINCT t2.RowNo, ( SELECT t1.MyWord + ' ' AS [text()] From @words AS t1 Where t1.RowNo = t2.RowNo ORDER BY t1.PartNo For XML PATH ('') ) AS MySentence From @words AS t2
Result:
RowNo MySentence 1 This is very strange text... 2 This is very stupid text !!! 3 This is another very strange text.
这篇关于使用sql查询删除单词之间的空格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!