how to remove characters char(0) to char(31)

Question

Post reply

how to remove characters char(0) to char(31)

Raymond van Laake

SSCarpal Tunnel

Points: 4212
More actions
November 16, 2012 at 6:21 am

#264854

Hi there,
Sometimes customers add data with unwanted characters in the ascii range 0 to 31. How do I remove them? I'd prefer a regular expression type of solution because this would be fastest.
Thanks,
Raymond

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Tee Time Hall of Fame Points: 3745 More actions · Answer 1

something like this may work for your purpose, but I think that may depend on

SELECT REPLACE(REPLACE(yourfield, CHAR(13), ''), CHAR(10), '')

This basically replaces the char with nothing.

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 2

It would work indeed, but I'm hoping to find a better way

Thx!

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

I'd go with nested REPLACEs. It's not going to look nice but I don't think there's a better way.

Perhaps wrap it in a Function if you need to reuse or to keep code tidy?

Lowell SSC Guru Points: 323518 More actions · Answer 4

i have this scalar function saved in my snippets, that basically strips chars that don't fit within desired ranges;

this strips your 0-31, but also spaces, punctuations and all high ascii chracters as well; it's a little greedy with the deletes, but a great example to modify.

you could modify it to fit your specific needs:

CREATE FUNCTION StripNonAlphaNumeric(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE @CleanedText VARCHAR(8000)

;WITH tally (N) as

(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)

FROM Master.dbo.SysColumns sc1

CROSS JOIN Master.dbo.SysColumns sc2)

SELECT @CleanedText = ISNULL(@CleanedText,'') +

CASE

--ascii numbers are 48(for '0') thru 57 (for '9')

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57

THEN SUBSTRING(@OriginalText,Tally.N,1)

--ascii upper case letters A-Z is 65 thru 90

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 65 AND 90

THEN SUBSTRING(@OriginalText,Tally.N,1)

--ascii lower case letters a-z is 97 thru 122

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 97 AND 122

THEN SUBSTRING(@OriginalText,Tally.N,1)

ELSE '' END

FROM tally WHERE Tally.N <= LEN(@OriginalText)

RETURN @CleanedText

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101083 More actions · Answer 5

I too think nested REPLACEs would probably be fastest.

You could do a simple function, but I'm not sure that would perform as well, let alone better ... but, really, with SQL, it's hard to tell so you may want to give it a try :-).

CREATE FUNCTION dbo.RemoveSpecifiedCharsFromString (

@string varchar(2000),

@charsToRemove varchar(50)

)

RETURNS varchar(2000)

AS

BEGIN

WHILE PATINDEX('%[' + @charsToRemove + ']%', @string) > 0

SET @string = STUFF(@string, PATINDEX('%[' + @charsToRemove + ']%', @string), 1, '')

RETURN @string

END --FUNCTION

DECLARE @string varchar(2000)

DECLARE @charsToRemove varchar(50)

SET @charsToRemove = CHAR(00) + CHAR(01) + CHAR(02) + CHAR(03) + CHAR(04) + CHAR(05) + /*... + */

CHAR(09) + CHAR(10) + /* ... + */ CHAR(13) + /* ... + */ CHAR(31)

SELECT string, dbo.RemoveSpecifiedCharsFromString(string, @charsToRemove)

FROM (

SELECT 'abc' + CHAR(10) + CHAR(13) + CHAR(01) + 'def' AS string UNION ALL

SELECT 'ghi' + CHAR(03) + CHAR(04) + REPLICATE(CHAR(05), 10) + 'jkl'

) AS test_data

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 6

Thank you all.... wil test your suggestions on Monday!

Ray

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 7

Raymond van Laake (11/16/2012)
Hi there,
Sometimes customers add data with unwanted characters in the ascii range 0 to 31. How do I remove them? I'd prefer a regular expression type of solution because this would be fastest.
Thanks,
Raymond

Regular expressions may be the fastest in another language but they're not necessarily the fastest in T-SQL because of the bit of overhead that a CLR to call RegEx would take. Please see the following article and the comprehensive discussion (click on "Join the Discussion") attached to that for proof.

http://www.sqlservercentral.com/articles/RegEx/88586/

Scott is correct, though. Nested REPLACEs will be faster than most anything else I can come up with especially when you create and use a high performance Inline Table Valued Function instead of using a Scalar UDF.

Most people also forget about the control character at the other end of the basic ASCII table, the DELETE character.

Here's the function that uses nested REPLACEs...

CREATE FUNCTION dbo.DropControlCharacters

(@pString VARCHAR(8000))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

SELECT CleanedString =

REPLACE(

@pString

,CHAR(0),'') COLLATE Latin1_General_BIN

,CHAR(1),'') COLLATE Latin1_General_BIN

,CHAR(2),'') COLLATE Latin1_General_BIN

,CHAR(3),'') COLLATE Latin1_General_BIN

,CHAR(4),'') COLLATE Latin1_General_BIN

,CHAR(5),'') COLLATE Latin1_General_BIN

,CHAR(6),'') COLLATE Latin1_General_BIN

,CHAR(7),'') COLLATE Latin1_General_BIN

,CHAR(8),'') COLLATE Latin1_General_BIN

,CHAR(9),'') COLLATE Latin1_General_BIN

,CHAR(10),'') COLLATE Latin1_General_BIN

,CHAR(11),'') COLLATE Latin1_General_BIN

,CHAR(12),'') COLLATE Latin1_General_BIN

,CHAR(13),'') COLLATE Latin1_General_BIN

,CHAR(14),'') COLLATE Latin1_General_BIN

,CHAR(15),'') COLLATE Latin1_General_BIN

,CHAR(16),'') COLLATE Latin1_General_BIN

,CHAR(17),'') COLLATE Latin1_General_BIN

,CHAR(18),'') COLLATE Latin1_General_BIN

,CHAR(19),'') COLLATE Latin1_General_BIN

,CHAR(20),'') COLLATE Latin1_General_BIN

,CHAR(21),'') COLLATE Latin1_General_BIN

,CHAR(22),'') COLLATE Latin1_General_BIN

,CHAR(23),'') COLLATE Latin1_General_BIN

,CHAR(24),'') COLLATE Latin1_General_BIN

,CHAR(25),'') COLLATE Latin1_General_BIN

,CHAR(26),'') COLLATE Latin1_General_BIN

,CHAR(27),'') COLLATE Latin1_General_BIN

,CHAR(28),'') COLLATE Latin1_General_BIN

,CHAR(29),'') COLLATE Latin1_General_BIN

,CHAR(30),'') COLLATE Latin1_General_BIN

,CHAR(31),'') COLLATE Latin1_General_BIN

,CHAR(127),'') COLLATE Latin1_General_BIN

;

GO

If you want to test performance, here's some code to build a wad o' test data. Details, as usual, are in the comments in the code.

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

--===== Create and populate the test table with test data.

-- Most rows will have 2 embedded control characters although some may have just 1

-- just due to random selection.

SELECT TOP 100000

RowNum = IDENTITY(INT,1,1),

SomeString =

STUFF(

'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz`~!@#$%^&*()_-+={[}]|\:;"''<,>.?/',

ABS(CHECKSUM(NEWID()))%94+1, 1, CHAR(ABS(CHECKSUM(NEWID()))%30+1)),

ABS(CHECKSUM(NEWID()))%94+1, 1, CHAR(ABS(CHECKSUM(NEWID()))%30+1))

INTO #TestTable

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

;

GO

Here's how to use the iTVF (iSF because it returns a scalar value) function against the test data.

SELECT ca.CleanedString

FROM #TestTable tt

CROSS APPLY dbo.DropControlCharacters(tt.SomeString) ca

;

--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)

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 8

Sorry... duplicate post removed.

--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)

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 9

Tee Time (11/16/2012)
something like this may work for your purpose, but I think that may depend on
SELECT REPLACE(REPLACE(yourfield, CHAR(13), ''), CHAR(10), '')
This basically replaces the char with nothing.

Raymond van Laake (11/16/2012)
It would work indeed, but I'm hoping to find a better way
Thx!

Define "better" because if those are the only two characters you really need to worry about, then you just blew off the absolute best way. 😉

--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)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

Jeff Moden (11/17/2012)

Raymond van Laake (11/16/2012)
...
Regular expressions may be the fastest in another language but they're not necessarily the fastest in T-SQL because of the bit of overhead that a CLR to call RegEx would take. Please see the following article and the comprehensive discussion (click on "Join the Discussion") attached to that for proof.
http://www.sqlservercentral.com/articles/RegEx/88586/
...

Hi Jeff, I think we already had couple discussions about this use of CLR with RegEx...

Let's I try to get it from another side.

1. Would anyone write nested REPLACE's in every query or procedure where do you need to apply the same logic? Most probably No!

2. Would anyone wrap it in UDF function which would take as input not only the string to operate on, but also the string which will contain all characters you want to replace? Most probably No!

What would you do?

I guess, most of developers, would created UDF which will take the input string and apply hard-coded nested REPLACE logic to clean it (I agree it would be the fasted option implemented in T-SQL) .

Am I right on the above?

If yes, why should we compare properly written UDF with CLR which does things listed in #2?

The CLR function from David's article is plainly a wrapper around RegEx object. It's never going to be the best option for performance, even if it would be just an C# function used solely within other C# code/application.

However, if you would implement CLR function, applying the same principals as for our best UDF, eg. dedicated function to clean the string, and code it properly (eg. defining static RegEx object with fixed, compiled expression) then you might be surprised how different performance will be. And it was already proved on this forum few times 😉

I've tried to find my post with performance states on this, but looks like it was quite long ago, only managed to find one where you can see how RegEx should be really used within CLR.

http://www.sqlservercentral.com/Forums/Topic1376365-391-2.aspx#bm1377606

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 11

Eugene Elutin (11/19/2012)

Jeff Moden (11/17/2012)
Raymond van Laake (11/16/2012)
...
Regular expressions may be the fastest in another language but they're not necessarily the fastest in T-SQL because of the bit of overhead that a CLR to call RegEx would take. Please see the following article and the comprehensive discussion (click on "Join the Discussion") attached to that for proof.
http://www.sqlservercentral.com/articles/RegEx/88586/
...
Hi Jeff, I think we already had couple discussions about this use of CLR with RegEx...
Let's I try to get it from another side.
1. Would anyone write nested REPLACE's in every query or procedure where do you need to apply the same logic? Most probably No!
2. Would anyone wrap it in UDF function which would take as input not only the string to operate on, but also the string which will contain all characters you want to replace? Most probably No!
What would you do?
I guess, most of developers, would created UDF which will take the input string and apply hard-coded nested REPLACE logic to clean it (I agree it would be the fasted option implemented in T-SQL) .
Am I right on the above?
If yes, why should we compare properly written UDF with CLR which does things listed in #2?
The CLR function from David's article is plainly a wrapper around RegEx object. It's never going to be the best option for performance, even if it would be just an C# function used solely within other C# code/application.
However, if you would implement CLR function, applying the same principals as for our best UDF, eg. dedicated function to clean the string, and code it properly (eg. defining static RegEx object with fixed, compiled expression) then you might be surprised how different performance will be. And it was already proved on this forum few times 😉
I've tried to find my post with performance states on this, but looks like it was quite long ago, only managed to find one where you can see how RegEx should be really used within CLR.
http://www.sqlservercentral.com/Forums/Topic1376365-391-2.aspx#bm1377606

Hi Eugene,

To answer question #1, I agree. No.

To answer question #2, I agree. No. It's better to write something specific as you say. Generic code usually only has generic performance.

To answer the rest, the only way to know for sure is to run a test or two. If someone would create a CLR to drop characters 0 thru 31 and 127, script it out and send it to me (I don't have a C# environment setup and wouldn't know what to do with it if I did), I'd be happy to test it, publish the results, and share the documented test code.

--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)

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 12

Jeff Moden (11/19/2012)

Eugene Elutin (11/19/2012)
Jeff Moden (11/17/2012)
Raymond van Laake (11/16/2012)
...
Regular expressions may be the fastest in another language but they're not necessarily the fastest in T-SQL because of the bit of overhead that a CLR to call RegEx would take. Please see the following article and the comprehensive discussion (click on "Join the Discussion") attached to that for proof.
http://www.sqlservercentral.com/articles/RegEx/88586/
...
Hi Jeff, I think we already had couple discussions about this use of CLR with RegEx...
Let's I try to get it from another side.
1. Would anyone write nested REPLACE's in every query or procedure where do you need to apply the same logic? Most probably No!
2. Would anyone wrap it in UDF function which would take as input not only the string to operate on, but also the string which will contain all characters you want to replace? Most probably No!
What would you do?
I guess, most of developers, would created UDF which will take the input string and apply hard-coded nested REPLACE logic to clean it (I agree it would be the fasted option implemented in T-SQL) .
Am I right on the above?
If yes, why should we compare properly written UDF with CLR which does things listed in #2?
The CLR function from David's article is plainly a wrapper around RegEx object. It's never going to be the best option for performance, even if it would be just an C# function used solely within other C# code/application.
However, if you would implement CLR function, applying the same principals as for our best UDF, eg. dedicated function to clean the string, and code it properly (eg. defining static RegEx object with fixed, compiled expression) then you might be surprised how different performance will be. And it was already proved on this forum few times 😉
I've tried to find my post with performance states on this, but looks like it was quite long ago, only managed to find one where you can see how RegEx should be really used within CLR.
http://www.sqlservercentral.com/Forums/Topic1376365-391-2.aspx#bm1377606
Hi Eugene,
To answer question #1, I agree. No.
To answer question #2, I agree. No. It's better to write something specific as you say. Generic code usually only has generic performance.
To answer the rest, the only way to know for sure is to run a test or two. If someone would create a CLR to drop characters 0 thru 31 and 127, script it out and send it to me (I don't have a C# environment setup and wouldn't know what to do with it if I did), I'd be happy to test it, publish the results, and share the documented test code.

Source code

using System;

using System.Data.SqlTypes;

using System.Text;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static String RemoveCtrlChars(String str)

{

StringBuilder sb = new StringBuilder(str.Length);

foreach (char ch in str)

{

if ((int)ch > 31 && (int)ch != 127)

{

sb.Append(ch);

}

return sb.ToString();

}

};

Create script

CREATE ASSEMBLY [SQLCLR]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300502BAA500000000000000000E00002210B0108000008000000060000000000007E2600000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000002826000053000000004000007003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000084060000002000000008000000020000000000000000000000000000200000602E72737263000000700300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E0000000000000000000000000000400000420000000000000000000000000000000060260000000000004800000002000500A4200000840500000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002004000000001000011026F0E00000A730F00000A0A020C160D2B1E08096F1000000A0B071F1F310D071F7F2E0806076F1100000A260917580D09086F0E00000A32D9066F1200000A2A1E02281300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000E0010000237E00004C0200007402000023537472696E677300000000C00400000800000023555300C8040000100000002347554944000000D8040000AC00000023426C6F620000000000000002000001471502000900000000FA0133001600000100000010000000020000000200000001000000130000000B00000001000000010000000200000000000A0001000000000006003A0033000600690057000600A10082000600B50057000600CE0057000600E900570006000401570006001D0157000600360157000600550157000600720157000600A90189010600C90189010A001502FA0106002E02330006004C02400200000000010000000000010001000100100015000000050001000100502000000000960041000A0001009C2000000000861851000F000200000001002A02110051001300190051001800210051001300290051001300310051001300390051001300410051001300490051001300510051001300590051001300610051001D00690051000F00710051000F00790035022700810051001D0079005A022B0081006402300009006B023600090051000F0020006B0022002E003B0075002E00230042002E002B0048002E00330069002E00130042002E00430042002E004B0042002E00530069002E005B0084002E0063008D003A0004800000010000006212685A000000000000E701000002000000000000000000000001002A00000000000200000000000000000000000100EE010000000000000000003C4D6F64756C653E0053514C434C522E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740052656D6F76654374726C4368617273002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053514C434C520053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650073747200537472696E67006765745F4C656E6774680053797374656D2E5465787400537472696E674275696C646572006765745F436861727300417070656E6400546F537472696E67000003200000000000BCC77A7EFB0CC14092426E5FE3F812920008B77A5C561934E0890400010E0E03200001042001010E0420010102042001010804010000000320000804200103080520011241030320000E0707041241030E080501000000002001001B436F7079726967687420C2A9204D6963726F736F6674203230313200000B01000653514C434C5200000E0100094D6963726F736F667400000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773015026000000000000000000006E2600000020000000000000000000000000000000000000000000006026000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000180300000000000000000000180334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100685A621200000100685A62123F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00478020000010053007400720069006E006700460069006C00650049006E0066006F00000054020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000380007000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C0043004C0052000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003700300036002E00320033003100340034000000000038000B00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310032000000000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052002E0064006C006C0000000000300007000100500072006F0064007500630074004E0061006D00650000000000530051004C0043004C0052000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003700300036002E00320033003100340034000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003700300036002E003200330031003400340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000803600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION [dbo].[RemoveCtrlChars](@str [nvarchar](4000))

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [SQLCLR].[UserDefinedFunctions].[RemoveCtrlChars]

GO

SELECT dbo.RemoveCtrlChars('abc'+CHAR(127)+'def')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 13

Thanks, Mark, That's perfect.

I'll setup the test code tonight after work.

--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)

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 14

@mark-3,

That was also pretty quick. Have you been using such a thing? If so, what's your take on performance here (And, yes, I still do the test because I said I would and because I'm insanely curious about these things. 🙂 )

--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)