Tech
How to extract alpha or numbers from a string within a SELECT statement WITHOUT a function call
On December 11, 2021 by Morthe StandardI am using the following SQL to create a function to strip out characters or numbers.
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END
I am calling it like this to extract the alpha characters and then the numeric characters into two sort fields:
SELECT
...
(SELECT dbo.fn_StripCharacters(PD.District, '^a-z')) AS Sort1,
CAST((SELECT dbo.fn_StripCharacters(PD.District, '^0-9')) AS INT) AS Sort2,
...
FROM
I am searching a LOT of Postcode District Records and the constant calls to the function are causing a comparatively big delay. Is there a way of reproducing this functionality without a function call? Can it be incorporated into the SELECT statement somehow?
SQL Server Version is 18.9.2
Table of Contents
You may also like
Recent Posts
The Importance of Innovation Management in Business Success
How to Measure Lab Diamond Ring Size
Web hosting plan: pro and cons of shared hosting and VPS hosting
White Sapphire vs. Diamond: The Ultimate Comparison
How Pawnbroking Works: A Step-by-Step Guide to Pawn Loans
GH Express LLC: Your Strategic Partner for Business Success in the U.S.
Novita Diamonds Shines a Light on Women’s Empowerment with Dress for Success Partnership
Buying Ethereum Down Under: Your Guide to Purchasing ETH in Australia
Exploring the Brilliance of Lab-Grown Diamonds: Understanding the 4Cs
Lab Diamonds: The Top Choice for Ethical, Affordable, and Sustainable Brilliance
SEO Backlink Services and Template Customization by a Pennsylvania SEO Expert
The Sparkle of Sustainability: Lab Grown Diamonds Adelaide
Crafting Love Stories: The Allure of Custom Made Engagement Rings
Beyond Numbers: Cultivate Meaningful Connections with Free Instagram Followers
How to Create a Logo in Turbologo
Popular Posts
Leveraging User Forums and Communities: Online iPhone Selling
Why You Should Be Adding Content to Google My Business
360-Degree Digital Marketing Services: What’s included?
What are the Different Types of Marketing?
5 Tips for Sharing Files and Information Online
6 Major Factors To Consider Before You Hire Marketing Agencies Auckland
First-Time Buyer’s Guide to Industrial Sewing Machines
Benefits of Hiring a Full Time SEO Specialist
Tips For Effective And Appealing Web Design
3 Biggest Strategies That Can Help You Scale Up Your Company
Using An SEO Consultant To Amp Up Your SEO Strategy During COVID-19
- What Is The Essence Of Enterprise Resource Management Systems
How To Make Your Air Conditioning Business Ready For Google?
Why Should You Add Comments To Your Blog?
3 Reasons Why Forecasting Sales Is Important For Businesses
Most Viewed Posts
API Integration Best Practices: Ensuring Secure and Scalable Solutions
On The Whole Learning Elaborating Open Source API Tools
Eliminate Annoyance By Fixing These Google Drive Problems
Reasons To Kick-Start Node JS Centric Product Development With Full-Swing
Reasons Why It Is Important to Select the Best Online Education Platform for a Programming Assignment
6 Benefits of Using a Good Website Builder
Techinques To Take Services For App Developers
- Four compelling reasons why the cloud makes it easy is the ideal IoT application
A Guide To Hiring The Best Web Development Company
The Best Tools For Mobile-First Indexing Strategy Development