XPost: microsoft.public.sqlserver.programming
From: genew@ocis.net
Dear SQLers:
I have finished writing my first involved stored procedure. It
makes strings nice. It works, but it might be better.
Have I, for example, missed out on functions that could have made
it easier?
replace() does not work as I expected when the search string is
partly contained in the result of a replace
replace(N'three spaces',N' ',N' ')
will result in
N'three spaces'
not
N'three spaces'
That is why I loop.
I also do not know how SQL Server handles multi-line values. I
am assuming there is a CR (or UniCode equivalent) to delimit lines. In
case there could be LFs, I eliminate them in the line flagged
"--*****TRY".
Test code follows.
What do you think?
***** Start of Code *****
-- NiceString
-- This procedure makes a string "nice". "nice" means that each line
of the
-- string has:
-- 1) no occurrences of (each replaced with space), and
-- 2) no leading spaces (removed) (optional),
-- 3) no trailing spaces (removed) (optional),
-- 4) no two spaces in a row (squeezed to one space).
-- The four points are applied in order 1-4.
-- "cLines": count of lines in the string. This is defined as the
number of
-- line delimiters in the string + 1. e.g. (where is a line
delimiter):
-- N'' has one line.
-- N'kitty' has one line.
-- N'This is the first line,
-- and this is the last line.' has two lines.
-- N'This is the first line,
-- this is the middle line,
-- and this is the last line.' has three lines.
-- If mLines=0, returns cLines.
-- If mLines>0 and cLines<=mLines, returns cLines.
-- If mLines>0 and cLines>mLines, returns a value >mLines.
create procedure NiceString
@strNiceMe nvarchar(max) output, -- the string to make nice and the
result
@mLines int=0, -- maximum number of lines permitted in string
-- If 0, the string can have any number of lines. If >0 and the
string
-- turns out to have more lines, processing will be aborted and the
string
-- will not be changed.
@fTrimLead bit=1, -- trim the leading blanks on each line? 0: no,
1: yes
@fTrimTrail bit=1 -- trim the trailing blanks on each line? 0: no,
1: yes
with recompile
as
begin
declare @strWork nvarchar(max)=@strNiceMe -- working version of
string
declare @strOut nvarchar(max)=N'' -- result string so far
declare @cLines int=1 -- number of lines (always at least one)
declare @strLine nvarchar(max) -- the current line being processed
declare @chrLineDelim nchar(1)=nchar(13) -- CR
declare @chrLF nchar(1)=nchar(10) -- LF
declare @chrTAB nchar(1)=nchar(9) -- TAB
declare @iLineDelim int -- where next line delimiter is in
@strWork
declare @cLeading int -- how many leading spaces in line
declare @cTrailing int -- how many trailing spaces in line
declare @strPrev nvarchar(max) -- previous version of line
while len(@strWork)>0 and (@mLines=0 or @cLines<=@mLines)
begin
-- Get the next line and remove it from strWork.
select @iLineDelim=patindex(N'%'+@chrLineDelim+N'%',@strWork)
if @iLineDelim>0
begin
select @strLine=left(@strWork,@iLineDelim-1)
select
@strWork=substring(@strWork,@iLineDelim+1,len(@strWork))
select @cLines=@cLines+1
end
else
begin
select @strLine=@strWork
select @strWork=N''
end
-- Trimming and removal of tabs
select @strLine=replace(@strLine,@chrLF,N'') --*****TRY
select @strLine=replace(@strLine,@chrTAB,N' ')
-- If either end is not to be trimmed, then the leading and
trailing
-- spaces will have to be restored after the line is squeezed.
if @fTrimLead=1 and @fTrimTrail=1
begin
select @cLeading=0
select @cTrailing=0
end
else
begin
select @cLeading=patindex(N'%[^ ]%',@strLine)
if @cLeading=len(@strLine) -- Line is only blanks.
select @cTrailing=0
else
select @cTrailing=patindex(N'%[^ ]%',reverse(@strLine))
end
select @strLine=ltrim(rtrim(@strLine))
-- Compression of consecutive spaces
select @strPrev=N''
while @strLine<>@strPrev
begin
select @strPrev=@strLine
select @strLine=replace(@strLine,N' ',N' ')
end
-- Add back any leading or trailing spaces being kept.
if @fTrimLead=0
select @strLine=space(@cLeading)+@strLine
if @fTrimTrail=0
select @strLine=@strLine+space(@cTrailing)
-- Add modified line to @strOut.
if len(@strOut)>0
select @strOut=@strOut+@chrLineDelim
select @strOut=@strOut+@strLine
end
if @mLines=0 or @cLines<=@mLines -- Replace original string if no
error.
select @strNiceMe=@strOut
return @cLines
end
***** End of Code *****
***** Start of Test Code *****
declare @strTestValue nvarchar(max)=
N' This is an example. '
declare @strTest nvarchar(max)
declare @RetVal int
select N'raw string',N'>'+@strTestValue+N'<'
select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output
select 'defaults not given',@RetVal as N'Ret Val',N'>'+@strTest+N'<'
select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output,0,1,1
select 'defaults given',@RetVal as N'Ret Val',N'>'+@strTest+N'<'
select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output,0,0,0
select '0,0,0',@RetVal as N'Ret Val',N'>'+@strTest+N'<'
select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output,0,1,0
select '0,1,0',@RetVal as N'Ret Val',N'>'+@strTest+N'<'
select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output,0,0,1
select '0,0,1',@RetVal as N'Ret Val',N'>'+@strTest+N'<'
select @strTest=N''
execute @RetVal=NiceString @strTest output
select 'empty, defaults not given',@RetVal as N'Ret
Val',N'>'+@strTest+N'<'
select
len(@strTest) as Length,
patindex(N'%'+nchar(13)+N'%',@strTest) as N'First LD'
select @strTest=
N' This is a three line string. '+nchar(13)+
N' This is the second line. '+nchar(13)+
N' This is the last line. '
execute @RetVal=NiceString @strTest output
select 'defaults not given',@RetVal as N'Ret Val',N'>'+@strTest+N'<'
select
len(@strTest) as Length,
patindex(N'%'+nchar(13)+N'%',@strTest) as N'First LD'
select @strTest=
N' This is a three line string. '+nchar(13)+
N' This is the second line. '+nchar(13)+
N' This is the last line. '
execute @RetVal=NiceString @strTest output,3
[continued in next message]
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
