September 4, 2009 at 8:33 am
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 '
September 4, 2009 at 8:41 am
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
Consultant - Straight Path Solutions
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
September 4, 2009 at 8:48 am
'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
September 4, 2009 at 9:02 am
For that given string, this worked for me:
select rtrim(ltrim(replace(replace(@string,' ',''),' ','')))
September 4, 2009 at 9:11 am
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.
September 4, 2009 at 10:02 am
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
September 4, 2009 at 10:13 am
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
September 6, 2009 at 8:16 am
select rtrim(ltrim(replace(replace('z z',' ',''),' ','')))
zz Instead of z z
September 7, 2009 at 5:15 am
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.
September 7, 2009 at 9:29 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 8, 2009 at 12:02 am
Folks, it doesn't need to be ugly (although this won't take care of the double spaces at the end of a sentence) and it doesn't need to be difficult and it certainly doesn't need to use a loop and/or a UDF. Now, I apologize for not having a bunch of test code to prove what I'm about to say, but I won't have access to SQL Server for about another week so I'll just have to try and explain. I didn't invent this method.. I just improved on it a bit. And I appologize for not having the person's name where I first saw it at my finger tips.
I'll use the letter "O" to act as a "visible" space so you can see what I'm talking about... consider the following sets of spaces, please....
O
OO
OOO
OOOO
OOOOO
OOOOOO
OOOOOOO
OOOOOOOO
Remember the goal is to convert all of those sets of spaces to just a single space without the use of RBAR even if the RBAR is through the simple use of a UDF. I'll also state that the goal is to remove the extra spaces without making the original string any larger in the process because it may already be as large as it can be for the given datatype.
So... STEP 1 is to identify pairs of spaces. This is done by modifying the second space in each pair of spaces to be an "unlikely" character. In this case, I'll use the visible character of "X" (which isn't unlikely but serves this visual example) to represent a space that has been changed in a pair of spaces. When we replace all pairs of space "OO" with "OX", we get the following
O
OX
OXO
OXOX
OXOXO
OXOXOX
OXOXOXO
OXOXOXOX
STEP 2 is to replace all occurances of "XO" with NOTHING...
O
OX
O[highlight]XO[/highlight]
O[highlight]XO[/highlight]X
O[highlight]XO[/highlight][highlight]XO[/highlight]
O[highlight]XO[/highlight][highlight]XO[/highlight]X
O[highlight]XO[/highlight][highlight]XO[/highlight][highlight]XO[/highlight]
O[highlight]XO[/highlight][highlight]XO[/highlight][highlight]XO[/highlight]X
... and that leaves us with ...
O
OX
O
OX
O
OX
O
OX
STEP 3 is to replace "X" with NOTHING...
O
O[highlight]X[/highlight]
O
O[highlight]X[/highlight]
O
O[highlight]X[/highlight]
O
O[highlight]X[/highlight]
... and that leaves us with just singles spaces everywhere...
O
O
O
O
O
O
O
O
Again... the "O"s represent unmodified spaces and the "X"s represent spaces changed to some unlikely character like maybe one of the graphic characters that occur above ASCII 127. [font="Arial Black"]It's just a matter of 3 nested REPLACE statements to handle ANY number of spaces[/font] to accomplish the things I laid out above. It can all be done in a single set-based query without loops or even UDF's.
Heh... please stop suggesting loops. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2009 at 8:15 am
Guys thanks to all. Everyone seems right except the one where he is only thinking about this particluar string. There might be different ways when data comes to me. The function that I posted in my previous post seems to work so far. However, it uses loops, but perfermonace is still good.
I will defnetaly think about other options, in the future if anything goes wrong my current plan.
thanks again to all
September 8, 2009 at 8:26 am
Darn it Jeff, I know I have seen that solution somewhere before, don't know if it was by you or not, but I wish I would have remembered it.:-D
Jack Corbett
Consultant - Straight Path Solutions
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
September 8, 2009 at 9:59 am
That is great code, I am curious if that can be adapted to a mild annoyance I have daily. We have situations where two fields need to be concatenated to get a full description or name. However, due to the vendor's system, in some cases there end up being spaces in the middle of a word. That is, I can easily get the two strings "Jim ", " Bob" to display as "Jim Bob", however, sometimes the name is a company, so a string pair like "Raccoon Ci "," ty General Hospital" could be in the same columns in the data. For the first situation I can use rtrim('Jim ') + ' ' + rtrim(' Bob') and get "Jim Bob". The same code on the company yeilds "Raccoon Ci ty General Hospital". As it turns out, the records have no indicator as to the data being a person or company, even on the vendor application the data is displayed with the odd space. It bugs me to have to send external data/reports out with these odd looking strings.
September 8, 2009 at 10:31 am
David, if you are talking about my code, then it seems it will not help for your second scenerio. ALL my function does is - removes spaces bewteen two or more words in a sentence ( leaves only one space where there are many spaces). Since you are doing concat , you will end up with space inbetwwen words in case of those type, unless there is way to find that if this is compnay name do -left + right and if name of person do left + '' + right.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply