August 17, 2009 at 8:44 am
Need 1 T-SQL statement which will remove "n" spaces from a string...
Example --- Change:
'MyFile .txt' to 'MyFile.txt'
'MyFile .txt to 'MyFile.txt'
'MyFile .txt to 'MyFile.txt'
August 17, 2009 at 8:47 am
If you want to remove all spaces, "replace(string, ' ', '')" will do it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 17, 2009 at 9:41 am
I would second GSquared's suggestion.
August 17, 2009 at 10:23 am
If you actually want to remove N spaces, IE you want your procedure / statement to have an input which will define the number of spaces you want to remove, you'll have to do a bit more work, since the REPLACE statement in T-SQL doesn't have an input specifying how many times you want to replace.
What you can do is, go through the input string character by character, and append to the output string each character you read in. Every time you encounter a space, you skip the space character and decrease your counter, and when the counter is zero, you append the rest of the string.
August 17, 2009 at 10:46 am
ok if there could be multiple spaces before the period,
here's how i would do it: i thought about STUFFing it, but relized there's a simpler solution:
i would simply grab the substring of everything left of the period, and RTRIM it, then use it to construct the rest of the filename:
create table #example(WhichFileName varchar(200))
insert into #example
SELECT 'MyFile .txt' UNION ALL
SELECT 'MyFile2 .txt' UNION ALL
SELECT 'A Long File Name .txt 'UNION ALL
SELECT 'Another File .txt'
--STUFF ( character_expression , start , length ,character_expression )
select WhichFileName,
RTRIM(substring(WhichFileName,1,CHARINDEX('.',WhichFileName) - 1)) +
substring(WhichFileName,CHARINDEX('.',WhichFileName) - 1,30)
from #example
Lowell
August 18, 2009 at 6:53 am
Lowell, I don't think that will work with file names like "My Long File . Has Extra . Periods . In . It .xls".
For something like that, if you just want to get rid of the spaces before the last period, the simplest solution is reverse the string, remove the spaces after what's now the first period, and then re-reverse it.
But that's not what was requested. The request was simply remove n spaces. Simple replace will do that. If you want to remove a specified number, then you would use Replicate to accomplish that efficiently:
declare @N int, @String varchar(max);
select @N = 6, @String = '1 2 3 4 5 6 x'; -- each number has a number of spaces after it equal the value of the number
select replace(@String, replicate(' ', @N), '');
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 2:22 pm
GSquared,
I don't recall the original poster mentioning whether those spaces had to be continuous. For example, what if you have a string like "My Good Boy Did A Good Deed Today And I Liked That." Taking 6 spaces out of this string has two potential problems (based on what we DON'T know): 1) which 6 spaces? The first 6, the last 6, or ???, and 2) what happens if in some cases, the 6 spaces you would choose to remove wouldn't be describable by the EXACT same rule you used on some other string?
Once you know which 6 to CONSISTENTLY remove (I'll assume the first N for the code below), then you could do as follows:
DECLARE @STRING AS varchar(1000), @SPACE_COUNT AS INT, @LOCATION AS INT
SET @STRING = 'My Good Boy Did A Good Deed Today And I Liked That.'
SET @SPACE_COUNT = 6-- THIS IS THE "N" REFERRED TO BY THE OP
SELECT @STRING AS THE_STRING_BEFORE
SELECT @LOCATION = N
FROM (
SELECT TOP 1 N
FROM (
SELECT TOP (@SPACE_COUNT) N
FROM master.dbo.Tally
WHERE N <= LEN(@STRING)
AND SUBSTRING(@STRING, N, 1) = ' '
ORDER BY N
) AS X
ORDER BY N DESC
) AS Y
SET @STRING = REPLACE(LEFT(@STRING, @LOCATION), ' ', '')
+ RIGHT(@STRING, LEN(@STRING) - @LOCATION)
SELECT @STRING AS THE_STRING_AFTER
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2009 at 3:00 pm
Yes. Depending on the complexities, we can come up with all kinds of ways of removing various patterns of strings from other strings.
I still think my first suggestion of using replace and a zero-length string is what was actually needed. We may never know, if the OP doesn't come back and reply.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 20, 2009 at 6:59 am
Need statement to remove empty space from a thread...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 20, 2009 at 9:46 am
As long as we're going down humor lane... how about a deadpan response?
DELETE FROM SQLSERVERCENTRAL
WHERE THREAD = ''
Steve
(aka smunson)
:-D:-D:-D
jcrawf02 (8/20/2009)
Need statement to remove empty space from a thread...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply