Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

ANYTHING THAT CAN HELP TRIM THE DATA FOR A GIVEN STRING... Expand / Collapse
Author
Message
Posted Friday, September 4, 2009 8:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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 '
Post #782969
Posted Friday, September 4, 2009 8:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:01 PM
Points: 10,295, Visits: 13,280
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

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
Post #782978
Posted Friday, September 4, 2009 8:48 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
'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




Post #782986
Posted Friday, September 4, 2009 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 29, 2010 8:33 AM
Points: 21, Visits: 118
For that given string, this worked for me:

select rtrim(ltrim(replace(replace(@string,' ',''),' ','')))
Post #782996
Posted Friday, September 4, 2009 9:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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.
Post #783004
Posted Friday, September 4, 2009 10:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 12,905, Visits: 32,180

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #783045
Posted Friday, September 4, 2009 10:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 12,905, Visits: 32,180
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #783053
Posted Sunday, September 6, 2009 8:16 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 11:01 AM
Points: 50, Visits: 246
select rtrim(ltrim(replace(replace('z z',' ',''),' ','')))
zz Instead of z z
Post #783472
Posted Monday, September 7, 2009 5:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 27, 2014 12:06 PM
Points: 357, Visits: 1,974
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.
Post #783707
Posted Monday, September 7, 2009 9:29 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:20 PM
Points: 1,670, Visits: 2,224
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)

Internet ATM Machine
Post #783973
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse