Archive

Archive for November, 2012

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
%d bloggers like this: