How to escape the ^ symbol in the following function

Question

How to escape the ^ symbol in the following function

Viewing 3 posts - 16 through 17 (of 17 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Abu Dina (8/16/2012)
Sorry
select cleanstring from dbo.isfn_QBRemoveNonAlphaChars('Abu???////Dina')
Gives back AbuDina so no spaces insrted. 🙁

Here's something I knocked up over lunch, shouldn't take long to swap the tally table for Jeff's:

SELECT d.string, x.StrippedString

FROM (

SELECT String = CAST('The` @Ca:685.4t - sa98,,,76764t 9999999X' AS VARCHAR(100)) UNION ALL

SELECT ' on t"£$h999e m-at12345GG'

) d

CROSS APPLY (

SELECT CAST(

(SELECT CASE WHEN CurChar LIKE('%[- A-Z]%') THEN CurChar ELSE '' END

FROM (

SELECT n = t1.n+t2.n

FROM (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1(n)

CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2(n)

WHERE t1.n+t2.n <= DATALENGTH(d.string)

) tally

CROSS APPLY (SELECT SUBSTRING(d.String,n,1)) x (CurChar)

FOR XML PATH(''), TYPE)

AS VARCHAR(100))

) x (StrippedString)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

Jeff Moden (8/16/2012)
Abu Dina (8/16/2012)
Sorry
select cleanstring from dbo.isfn_QBRemoveNonAlphaChars('Abu???////Dina')
Gives back AbuDina so no spaces insrted. 🙁
We've had a couple of different names for this function. There may be some confusion because the modification that Cadavre made works just fine. Make sure that you're executing the correct function.

The other thing to make sure of is to make sure there's a space between the two single quotes in the maod that cadavre made.

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

Abu Dina SSChampion Points: 14155 More actions · Answer 3

Yup, I'm officially a SQL n00b! (http://www.urbandictionary.com/define.php?term=n00b)

Thanks Jeff, Cadavre & Chris.

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

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[/url]

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