Home > T-SQL > T-SQL : An Interesting Question – 2

T-SQL : An Interesting Question – 2


Another interesting question that I have read recently, which involves some string operations.

Question:
The whole string given is a alphanumeric (for ex: AP1AA). The requirement is to Stuff ‘0’ before the single digit number (0-9) which is enclosed by Alphabets (AP1AA)).

For Ex:

If you are given a string like AP2JJ then it should be stuffed with a zero before the single digit numerical (2 in this case) and the output should be AP02JJ. When the numeric is of 2 digits, no actions are needed.

Query:

DECLARE @table TABLE (ExString varchar (12))
INSERT INTO @table
SELECT 'AP10AA' UNION
SELECT 'AA10B' UNION
SELECT 'AAA123' UNION
SELECT 'AA1AB1' UNION
SELECT 'AA2B1' UNION
SELECT 'AA03A' UNION
SELECT 'AA1234' UNION
SELECT '2BB' UNION
SELECT 'A9B' UNION
SELECT 'A0B' UNION
SELECT 'A10B'

SELECT
ExString AS ExString_Before ,
CASE WHEN ExString LIKE '%[a-z][0-9][a-z]%'
THEN STUFF (ExString, patindex('%[0-9]%' ,ExString), 0,'0' )
ELSE ExString
END ExString_After
FROM @table

I have shared my query to perform the above stuff operations. Any suggestions and new ideas are welcomed

Happy Querying 😉

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: