|
How to remove excessive spaces |
| | | | Submitted on: 10/31/2003 2:37:53 AM
By: Kaczyński Grzegorz
Level: Intermediate User Rating: Unrated Compatibility:SQL Server 2000, SQL Server 7.0
Users have accessed this article 948 times. | |
| | How to remove excessive spaces from column in your table? Example: how to convert 'dupa___jasiu______pierdzi___stasiu' to 'dupa_jasiu_pierdzi_stasiu' (tested only with varchar) :).
| | | Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description. |
DECLARE @2spc char(4)
SET @2spc='%'+SPACE(2)+'%'
WHILE EXISTS (SELECT * FROM your_table_name
WHERE CHARINDEX(SPACE(2),RTRIM(column_name))<>0)
BEGIN
UPDATE your_table_name
SET column_name=
SUBSTRING(column_name,1,CHARINDEX(SPACE(2),column_name)) +
LTRIM(SUBSTRING(column_name,CHARINDEX(SPACE(2),column_name),LEN(column_name)-CHARINDEX(SPACE(2),column_name)+1))
WHERE RTRIM(column_name) LIKE @2spc
AND SUBSTRING(column_name,CHARINDEX(SPACE(2),column_name)-1,1)<>SPACE(1)
END
| | | Report Bad Submission | | | Your Vote! |
See Voting Log | | Other User Comments | 11/1/2003 12:06:23 PM: aleœ sie popisa³ stary. Brawo brawo
brawo...
| | Add Your Feedback! | Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular article, please click here. | | |