SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ANYTHING THAT CAN HELP TRIM THE DATA FOR A GIVEN STRING...


ANYTHING THAT CAN HELP TRIM THE DATA FOR A GIVEN STRING...

Author
Message
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 565
I want to trim a given string so that ppl could see them without long empty spaces inbetween words.

Declare @myString Varchar(256)

SET @MY STRING = 'A COSMETOLOGIST uses knowledge to do things to a client`s hair. A HAIRAPIST uses knowledge to give a customer guidance so she can do her own "hair improvement".


--this is what I tired which works for left and right spaces and in some instances for spaces ----------- inbtween words.
SELECT REPLACE(LTRIM(RTRIM(@STRING)), ' ', ' ')

-- If you see above string there ar emore than one empty space between words
-- HAIRAPIST and uses and hair and Improvement.

this is how I like to see the results

A COSMETOLOGIST uses knowledge to do things to a client`s hair.A HAIRAPIST uses knowledge to give a customer guidance so she can do her own "hair improvement".


ANy help is appreciated '
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45411 Visits: 14925
I'm not sure what you want. I don't see any double spaces in the string you included. You may need to check for Tabs (Char(9)), Line Feeds (Char(10)), and Carriage Returns (Char(13)). I may have the codes for Line Feeds and Carriage Returns backwards.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 565
'A COSMETOLOGIST uses knowledge to do things to a client`s hair.  A HAIRAPIST    uses knowledge to give a customer guidance so she can do her own "hair          improvement".                                                                                   '



well, u don't see it becoz i posted as plain text. This might help here.
I do have a function, but I just don't want to use functions if there is alternatives.

here is code for function
CREATE function [dbo].[cleanString2](@string varchar(MAX))
returns varchar(MAX)
AS
Begin
if charindex(' ', @string) = 0 return @string
set @string = replace(@string, ' ',' ')
while charindex(' ', @string) > 0
select @string = dbo.cleanString2(@string) --recursive call

return rtrim(ltrim(@string))
End
-------------------------------------------------------------------------------------

CREATE function [dbo].[fn_RemoveWhtSpaces](@string varchar(MAX))
returns varchar(MAX)
AS
Begin
if charindex(' ', @string) = 0 return @string
set @string = replace(@string, ' ',' ')
while charindex(' ', @string) > 0
select @string = dbo.cleanString2(@string) --recursive call

return rtrim(ltrim(@string))
End





mcha7628
mcha7628
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 118
For that given string, this worked for me:

select rtrim(ltrim(replace(replace(@string,' ',''),' ','')))
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 565
Thanks MC.....It worked becoz u have two rplace there to take care of two scenerioes. But we don't know how it comes for other rows from mainframe. Rt now I am using the above funstions, which works fine.
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73379 Visits: 40969


Declare @myString Varchar(256)

SET @MY STRING = 'A COSMETOLOGIST uses knowledge to do things to a client`s hair. A HAIRAPIST uses knowledge to give a customer guidance so she can do her own "hair improvement".




there is 4 spaces after "HAIRAPIST", the replace would change from 4 spaces to two spaces, so it's misleading because there is still more whitespace than you expect to be there when you are done.

Hate to think a loop should be used, most likely a tally table solution would help strip out doubler spaces;
lemme try something and i'll post the results.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73379 Visits: 40969
here's how i would do it with an CTE Tally Table:


CREATE FUNCTION StripExtraSpaces(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;WITH tally (N) as
(SELECT TOP 1000000 row_number() OVER (ORDER BY sc1.id)
FROM SysColumns sc1
CROSS JOIN SysColumns sc2
CROSS JOIN SysColumns sc3)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE
--ascii numbers are 48(for '0') thru 57 (for '9')
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) = 32 --space
AND ASCII(SUBSTRING(@OriginalText,Tally.N -1 ,1)) = 32
THEN ''
ELSE SUBSTRING(@OriginalText,Tally.N,1)
END
FROM Tally
WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
GO
select dbo.StripExtraSpaces('will this strip out extraa whitespace, ')
--results: yes it will:
[will this strip out extraa whitespace,]



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
setiv
setiv
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 246
select rtrim(ltrim(replace(replace('z z',' ',''),' ','')))
zz Instead of z z
Ross McMicken
Ross McMicken
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1109 Visits: 2250
A nonelegant way to do this is to use a series of replace statements that make a set of 4 spaces 1 space, then 3 spaces 1 space, then 2 spaces 1 space.

replace(string, '    ',' ')
replace(string, ' ',' ')
replace(string, ' ',' ')



Alternatively, loop through a replace two space command until there's not any more spaces longer than one character.

I've done something similar in MS Word and in Notepad. I've probably done it in Query Analyzer as well.
sgmunson
sgmunson
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17592 Visits: 4642
This kind of thing can get ugly in a hurry. Stop and think about appearance of the text in a situation with multiple sentences, and you start to see where I'm going. If you decide that just one space after a sentence isn't sufficient white space, you have a very difficult problem to solve, and to be honest, just one space after a sentence and before another one is really trying on the eyes when one is trying to read.

The reason it becomes difficult is because you then have to differentiate between a period used as the end of a sentence vs. a period used as I just did with the abbreviation of the word "versus". Same problem is even more trouble when you think about the inclusion of names like St. John, or anything containing an address, like "12345 Shoemaker Rd.". And then there are decimal numbers to contend with. Honestly, you need a VERY specific definition of EXACTLY what you expect in the output, because it doesn't take much for this kind of thing to become either a serious performance problem or a nearly completely impractical idea.

On the positive side of things, one CAN examine the data and find the longest continuous string of spaces in existing data, and then determine the smallest power of 2 just greater than that number, and then use the power number as the number of REPLACEs that you do with one space replacing two.

Steve
(aka smunson)
:-):-):-)

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search