Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to escape the ^ symbol in the following function Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 8:33 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Good afternoon.

I have the following function:

 
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_QBRemoveNonAlphaChars]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_QBRemoveNonAlphaChars]
GO

CREATE FUNCTION [dbo].[fn_QBRemoveNonAlphaChars](@String VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN

WHILE PATINDEX('%[^a-z ''^-]%', @String) > 0
SET @String = STUFF(@String, PATINDEX('%[^a-z ''^-]%', @String), 1, '')

RETURN @String
END
GO

This works great most of the time, for example:

select dbo.[fn_QBRemoveNonAlphaChars]('The~@ CA:$%&*$()T -   SA0099878778747T ~~~;;;!||o*&")|n the ma@@@@@t')

returns: The CAT - SAT on the mat which is perfect

However, if I try it with:

select dbo.[fn_QBRemoveNonAlphaChars]('The^~@ CA:$%&*$()T -   SA0099878778747T ~~~;;;!||o*&")|n the ma@@@@@t')

It returns: The^ CAT - SAT on the mat

I've tried to escape it but I can't get it to work.

Any suggestions?

Thanks.


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1345303
Posted Wednesday, August 15, 2012 9:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 2,112, Visits: 5,427
The fast way of working around it will be to modify the function's last line, so insead of the the line
RETURN @String

it would be
RETURN REPLACE(@String, '^','')

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1345322
Posted Wednesday, August 15, 2012 9:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:17 AM
Points: 2,434, Visits: 7,513
Total guess, no testing has gone into this: -

CREATE FUNCTION [dbo].[fn_QBRemoveNonAlphaChars] (@String VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^a-z ''-]%', @String) > 0
SET @String = STUFF(@String, PATINDEX('%[^a-z ''-]%', @String), 1, '')

RETURN @String
END

You had the ^ character in your "allowed" pattern.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1345323
Posted Wednesday, August 15, 2012 9:10 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Cadavre, the ^ at the end is there to exclude the - from being removed.

Adi, I'm happy with your solution!

Thanks to you both.


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1345329
Posted Wednesday, August 15, 2012 9:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:17 AM
Points: 2,434, Visits: 7,513
Abu Dina (8/15/2012)
Cadavre, the ^ at the end is there to exclude the - from being removed.

Adi, I'm happy with your solution!

Thanks to you both.


Test mine ;)

Your code: -
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_QBRemoveNonAlphaChars]')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_QBRemoveNonAlphaChars]
GO

CREATE FUNCTION [dbo].[fn_QBRemoveNonAlphaChars] (@String VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^a-z ''^-]%', @String) > 0
SET @String = STUFF(@String, PATINDEX('%[^a-z ''^-]%', @String), 1, '')

RETURN @String
END
GO

SELECT dbo.[fn_QBRemoveNonAlphaChars](
'The^~@ CA:$%&*$()T - SA0099878778747T ~~~;;;!||o*&")|n the ma@@@@@t')

Returns: -
----------------------------
The^ CAT - SAT on the mat


My change: -
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_QBRemoveNonAlphaChars]')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_QBRemoveNonAlphaChars]
GO

CREATE FUNCTION [dbo].[fn_QBRemoveNonAlphaChars] (@String VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^a-z ''-]%', @String) > 0
SET @String = STUFF(@String, PATINDEX('%[^a-z ''-]%', @String), 1, '')

RETURN @String
END
GO

SELECT dbo.[fn_QBRemoveNonAlphaChars](
'The^~@ CA:$%&*$()T - SA0099878778747T ~~~;;;!||o*&")|n the ma@@@@@t')

Returns: -
---------------------------
The CAT - SAT on the mat



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1345340
Posted Wednesday, August 15, 2012 9:32 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Cadavre, my sincerest apologies!

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1345353
Posted Wednesday, August 15, 2012 9:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:17 AM
Points: 2,434, Visits: 7,513
Abu Dina (8/15/2012)
Cadavre, my sincerest apologies!


No problem. We're all here to learn and it's better to make a mistake on a forum than on production code, right?



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1345354
Posted Wednesday, August 15, 2012 9:40 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Hear hear

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1345356
Posted Wednesday, August 15, 2012 10:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:44 AM
Points: 12,910, Visits: 32,015
would you expect your function to remove high ascii characters as well?
i had posted a solution in your other thread on a similar strip function;
what would you expect for the results of this?
SELECT dbo.[fn_QBRemoveNonAlphaChars](
'ÀAlbèert ËEîinstêeiìn ÌInstìitúutëe - MPG')



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1345401
Posted Wednesday, August 15, 2012 12:28 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Hi Lowell,

Just got home to see your reply.

I don't have access to my work right now but your method is just easier to understand and adapt.

Will try again tomorrow and report back.

Thanks for your time, much appreciated.

Regards.


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1345456
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse