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
Popular Posts
Leveraging GenAI for Faster and Smarter Product EngineeringErfan Khoshnazar: A Global Education Influencer Empowering Students for the Digital Era
The Importance of Air Pollution Monitoring
Unlocking Efficiency: A Visual Guide to Enhancing Student Registration with WebLOAD and Ellucian Banner
Optimal Mobile Experience Mastering Mobile Optimization and Responsive Design
PSPO-II Exam Dumps: Unlock Your Success
LED Stromschienenstrahler und 3 Phasen Schienenstrahler
What Makes Mini Supvan Printer Better?
LP5120M Continuous Thermal Labels Use in Different Industries
Supvan Print Label: The Ultimate Tool for Labeling Needs
What are the Benefits of Using MFi-Certified Cables and Chargers for Your iPhone?
Ways Smartphones Can Be Used for Learning
Best Tips To Prepare for an Interview
Installation of H7 LED Headlight Bulb: A Piece of Cake or An Uphill Battle
Honor 70 Review Among Best 5g Phones Of 2022
Latest Posts
Bytgem and the Rise of Online Product Raffles: How Aussies Are Scoring Big
Timeless Sparkle: Choosing Between Sapphire and Diamond
How to Convert Leads with Ease
Experience Top-Notch CNC Turning Services at RADMOT
Slot Online Maxwin: Your Path to Jackpot ThrillsUnlocking the Potential of AI: A Glimpse into the Future
Exciting Developments in Peterborough: A Snapshot of Recent News
Exploring “The Georgia Bulletin”: A Beacon of Faith and Community
The Evolution of Media and Journalism: Navigating the Digital AgePolitical Turmoil Grips Sri Lanka as Economic Challenges Mount
Exploring TheMaineChronicle.com: A Dive into Media and Journalism
Trump in the News: The Continuing Influence and Controversy
Russia Latest News: Unraveling Developments from the Largest Country on Earth
Russia Latest News: A Comprehensive Overview of Current Developments
Russia Latest News: Key Developments in 2024
Featured Posts
Unleashing AI Power for Small Business Marketing Success
Pourquoi Choisir Une Structure En Acier Pour Vos Projets En Afrique ?
Top IPTV France Providers: Finding the Best Service for You
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
