April 18, 2008 at 11:18 am
Suppose I want to remove unnecessary carriage returns from a varchar without removing the necessary ones....
For example,
declare @t varchar(max)
set @t = 'test return
test return2
'
set @t = replace(@t, char(13) + char(10),'')
select @t
This will return the following with everything jammed together....
test returntest return2
I only want to remove the empty return between statements and return:
test return
test return2
Looking for a solution that doesn't loop through the entire string and cause huge performance issues (already have one of those)....
April 18, 2008 at 11:29 am
Replace 2 carraige returns next to each other with one:
set @t = replace(@t, char(10) + char(13) + char(10) + char(13),char(10) + char(13))
April 18, 2008 at 11:32 am
Chris Gierlack (4/18/2008)
Suppose I want to remove unnecessary carriage returns from a varchar without removing the necessary ones....For example,
declare @t varchar(max)
set @t = 'test return
test return2
'
set @t = replace(@t, char(10) + char(13),'')
select @t
This will return the following with everything jammed together....
test returntest return2
I only want to remove the empty return between statements and return:
test return
test return2
Looking for a solution that doesn't loop through the entire string and cause huge performance issues (already have one of those)....
If I'm understanding you right - you want to try something like:
set @t = replace(@t, char(10) + char(13)+char(10) + char(13),char(10) + char(13))
select @t
Meaning - find consecutive carriage returns and replace it with just one.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 18, 2008 at 11:33 am
Now that's what I call simple
Thanks...
April 18, 2008 at 12:02 pm
Hmmmm...thought that would work but when I executed this:
declare @t varchar(max)
set @t = 'test1
test2
'
select @t = replace(@t, char(10) + char(13) + char(10) + char(13), char(10) + char(13))
select @t
I got this:
test1
test2
April 18, 2008 at 12:06 pm
Because we are all idiots and had the line feed before the carraige return.
Try this:
select @t = replace(@t, char(13) + char(10) + char(13) + char(10), char(13) + char(10))
April 18, 2008 at 12:10 pm
Uhhhhhhh.....HA
I don't even have words for that one....yeah....
that worked....hehehehehe
thanks!
April 18, 2008 at 12:23 pm
hmm. It seems to at leaqst remove some of them for me.
Another, more reliable way - CLR and a Regex construct. That would give you:
set @t=dbo.regexreplace(@t,'(\r)+',char(13)+char(10)) -- the code is being chunked when posting - that should be "slash r slash n"
Where RegexReplace is a CLR function that looks like
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
Public Shared Function RegexReplace(ByVal input As SqlChars, ByVal pattern As SqlString, ByVal rep As SqlString) As SqlString
' Add your code here
Dim rex As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern.Value)
If input.IsNull Then
Return New SqlString(SqlString.Null.Value)
Else
Return New SqlString(rex.Replace(New String(input.Value), rep.Value))
End If
End Function
End Class
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 18, 2008 at 12:29 pm
Yeah that would definitely catch more of them, only problem is invoking the CLR has a good amount of overhead....
This will touch almost every procedure in the system....needs to be as lean as possible....
April 18, 2008 at 12:36 pm
Chris Gierlack (4/18/2008)
Uhhhhhhh.....HAI don't even have words for that one....yeah....
that worked....hehehehehe
thanks!
oops...:)
What can I say - Friday it is, hmm? (Yoda-style?)
By the way - for better or worse - if you have more than 2 in a row - you may need to run more than once (the T-SQL replace)....
And - I hear you about most CLR things, but Regex has yet to fail me. It's actually faster and leaner than pretty much anything T-SQL if you are looking for some kind of "non-basic" character-based pattern, especially to replace.
I've even managed to make Jeff Moden into a CLR Regex believer, and believe you me - that took some doing!:Whistling:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 18, 2008 at 12:41 pm
I blame it entirely on the initial post.
April 18, 2008 at 12:53 pm
Yeah I agree....in all things set-based, SQL is king but when it comes to string functions....not the best medium to work in
I'll setup a test harness and put regex to the test...
thanks.
April 18, 2008 at 12:54 pm
Thank god for edi...errrr....i mean, yeah, easy typo to make.....
April 21, 2008 at 9:01 am
Okay, I have to ask if this is being over-complicated. Why not just:
declare @test-2 varchar(100)
select @test-2 = '1
2
3'
select @test-2 = replace(@test, '
', '')
select @test-2
That works for me. I get "123".
Is there some reason to code out the char values or use a RBAR regex?
- 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
April 21, 2008 at 9:11 am
And, as per the original post, if you want to keep double-returns, and get rid of single, try something like this:
declare @test-2 varchar(100)
select @test-2 = '1
2
3'
select @test-2 = replace(replace(replace(@test, '
', '|'), '
', ''), '|', '
')
select @test-2
Gives:
1
23
If you simply want to get rid of double-returns:
replace(@test, '
', '
')
(You can nest that one in a While loop, which will get rid of triple, quadruple, etc., if you need to.)
- 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
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply