String Manipulation Help
-
I need some help regarding string manipulation, I'll setup the scenario:
I have a Table: Reports with a field called Path
Some examples of data in the Path field are:
C:\dirA \Report1.doc
C:\dirA \dir A2 \Report2.doc
C:\dirA\temp 2\Report 3.doc
...
As you can see some of the folder names have trailing white space. My task was to write a stored procedure that removes only trailing white space after the folder name(s) and no other spaces.
I did so with a couple While loops: the outer loop iterated through the records of the Report Table Updating the Path if it contained trailing white space after the folder name(s), while the inner loop removed trailing white space after folder name(s). It works fine, the output of my stored procedure is:
C:\dirA\Report1.doc
C:\dirA\dir A2\Report2.doc
C:\dirA\temp 2\Report 3.doc
...
However, I was then asked if I could condense my stored procedure into a single Update Statement.
My Question is: Is it possible to condense these 2 while loops into a single Update Statement?
I'm having trouble wrapping my head around it. If I was just trying to remove one trailing white space after the folder name I could use the following statement:
UPDATE Report SET Path = Replace(" \", "\", Path)
The thing is the number spaces can be anything (1 space, 2 spaces, 5 spaces, ...), so that makes things difficult.
I would appreciate any help or suggestions toward this matter, Thanks!
-Jonny
-
Absolutely... in the code that follows, only the third "paragraph" is required... the rest is just setting up for the test. Of course, you will need to delete all but the 3rd "paragraph" and change the name of the table to the actual name of your table prior to putting the code into production...
--===== Prepare a test table with data similar to what Jonathan posted
IF OBJECT_ID('TempDB..#Report') IS NOT NULL
DROP TABLE #Report
CREATE TABLE #Report (Path VARCHAR(8000))
INSERT INTO #Report (PATH)
SELECT 'C: \dirA \Report1.doc' UNION ALL
SELECT 'C: \dirA \dir A2 \Report2.doc' UNION ALL
SELECT 'C: \dirA \temp 2 \Report 3.doc'
--===== Display the contents of the test table before any changes
SELECT Path AS Before FROM #Report
--===== Execute the "single statement" update to fix the paths
-- THIS is the "paragraph" to eventually keep
WHILE @@ROWCOUNT IS NULL OR @@ROWCOUNT > 0
UPDATE #Report
SET Path = REPLACE(PATH,' \','\')
WHERE Path LIKE '% \%'
--===== Display the contents of the test table after the changes
SELECT Path AS After FROM #Report
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you expect a lot of rows with more than 1 space before the "\", this might be a lot faster...
--===== Execute the "single statement" update to fix the paths
-- THIS is the "paragraph" to eventually keep
WHILE @@ROWCOUNT IS NULL OR @@ROWCOUNT > 0
UPDATE #Report
SET Path = REPLACE(REPLACE(REPLACE(REPLACE(PATH,' \','\'),' \','\'),' \','\'),' \','\')
WHERE Path LIKE '% \%'
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I screwed up... that's what I get for late night programming... I don't know what on Earth made me think that @@ROWCOUNT would ever be a NULL. If you want this to work, you'll need to do something just before the code that will return a non-zero row count and the code only needs to look like this...
--===== Execute the "single statement" update to fix the paths
-- THIS is the "paragraph" to eventually keep
SELECT 1
WHILE @@ROWCOUNT > 0
UPDATE #Report
SET Path = REPLACE(REPLACE(REPLACE(REPLACE(PATH,' \','\'),' \','\'),' \','\'),' \','\')
WHERE Path LIKE '% \%'
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This will also work:
Declare @Path as nvarchar(1000)
Update aTableRecordCount
Set @Path = Replace( Path,' \', '\'),
@Path = Replace(@Path,' \', '\'),
@Path = Replace(@Path,' \', '\'),
@Path = Replace(@Path,' \', '\'),
@Path = Replace(@Path,' \', '\'),
@Path = Replace(@Path,' \', '\'),
@Path = Replace(@Path,' \', '\'),
Path = Replace(@Path,' \', '\')
WHERE Path LIKE '% \%'
-
you can test this function. the inputparameters are @value - field you want to get result
@trenn - in front of this character you want to trim spaces
create function dbo.test (@value as varchar(8000), @trenn as char(1))
returns varchar(8000) as
begin
declare @pos as integer
declare @lang as integer
declare @zahler as integer
declare @wert as varchar(8000)
set @value = rtrim(@value)
set @lang = len(@value)
set @zahler = 1
set @wert = ''
while @zahler <= @lang
begin
if substring(@value, @zahler, 1) <> @trenn
begin
set @wert = @wert + substring(@value, @zahler, 1)
end
if substring(@value, @zahler, 1) = @trenn
begin
set @wert = rtrim(@wert)
set @wert = @wert + @trenn
end
set @zahler = @zahler + 1
end
return @wert
end
SELECT dbo.test(<field_name>, '\')
-
Someone must frame this one and keep it posted on a board somewhere... That's just too good
.Here here to late nigth and closed eyes
. -
Heh... thanks for the feedback, Remi.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just teasing man
. -
Shoot... didn't mean to make it sound like I took it any other way... guess I need to use more smiley faces
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply