如何删除双空格并输入sql [英] how to remove double space and enter in sql
问题描述
HEllo Everyone,
我正在使用Sql Server 2005
i想删除双倍空格或输入从表的列,我使用以下代码
为此我使用RemoveSpaces函数
函数包含以下代码
CREATE 功能 RemoveSpaces
( @ InputString VARCHAR ( 1024 ))
RETURNS VARCHAR ( 1024 )
AS
BEGIN
WHILE CHARINDEX(' ', @ InputStrin g )> 0 - 检查双倍空格
SET @ InputString =
REPLACE(REPLACE (REPLACE(ISNULL( @ InputString ,' '), CHAR ( 13 ),' '), CHAR ( 10 ),' '),' < span class =code-string>',' ') - 将2个空格替换为1个空格
返回 @ InputString
END
现在如果我使用这个函数就像
选择 dbo.RemoveSpaces(' 3-2-205,Raja Mudliar Street,kalasiguda,Secunderabad' )
然后它的工作正常,但当我使用的功能如
选择 dbo.RemoveSpaces(afa.address) as addr 来自 table1
然后它无法正常工作......
你能告诉我问题出在哪里吗?
注意:避免使用不良英语
谢谢
Chetan V
删除多个空格...
d ECLARE @ s VARCHAR (MAX)= ' 221B BAKER STREET';
SELECT REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM( @s ))
,' ',' ' + CHAR( 7 ))
, CHAR ( 7 )+ ' ',' ')
, CHAR ( 7 ),' ') AS CleanString;
请参阅下面的链接。 ..
http:/ / davidbr ycehoward.com/archive/2011/05/more-recursionremoving-multiple-spaces/ [ ^ ]
删除换行符...
< pre lang =SQL> SELECT REPLACE(REPLACE( @ str , CHAR ( 13 ),' '), CHAR ( 10 ),' ')
Enhenced TRIM ()函数 - 删除尾随空格,前导空格,空格,制表符,回车符,换行符如下所示...
http://blog.sqlauthority.com/2008/10/10/sql-server-2008-enhenced-trim-function-remove-trailing-spaces -leading-spaces-white-space-tabs-carriage-returns-line-feeds / [ ^ ]
希望这对你有所帮助......
HEllo Everyone,
I am Using Sql Server 2005
i want remove double space or enter from the column of table for that i am using following code
For that I am using RemoveSpaces Function
the function Contain following code
CREATE FUNCTION RemoveSpaces
(@InputString VARCHAR(1024))
RETURNS VARCHAR(1024)
AS
BEGIN
WHILE CHARINDEX(' ',@InputString) > 0 -- Checking for double spaces
SET @InputString =
REPLACE(REPLACE(REPLACE(ISNULL( @InputString, ''), CHAR(13), ''), CHAR(10), ' '),' ',' ') -- Replace 2 spaces with 1 space
RETURN @InputString
END
Now If m using this function like
select dbo.RemoveSpaces('3-2-205, Raja Mudliar Street, kalasiguda, Secunderabad')
then its work fine but when m using function like
select dbo.RemoveSpaces(afa.address) as addr from table1
then its not working...
Can u tell me where is the problem?
Note: Avoid Bad English
Thanks
Chetan V
to remove multiple spaces ...
DECLARE @s VARCHAR(MAX) = '221B BAKER STREET'; SELECT REPLACE( REPLACE( REPLACE( LTRIM(RTRIM(@s)) ,' ',' '+CHAR(7)) ,CHAR(7)+' ','') ,CHAR(7),'') AS CleanString ;
refer below link...
http://davidbrycehoward.com/archive/2011/05/more-recursionremoving-multiple-spaces/[^]
to remove newline character..
SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds refer below...
http://blog.sqlauthority.com/2008/10/10/sql-server-2008-enhenced-trim-function-remove-trailing-spaces-leading-spaces-white-space-tabs-carriage-returns-line-feeds/[^]
hope this will help you...
这篇关于如何删除双空格并输入sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!