SQL Puzzle | Integers before and after CHR Puzzle | SQL Interview Question

In this puzzle you have to Identify records which contain Integer data before and after character P. For more details please refer sample input and expected output

Sample INPUT

Expected OUPUT

Use below script to create table and insert sample data into it.

--

CREATE TABLE OnlyIntsBeforeAndAfterP
(
	Vals VARCHAR(50)
)
GO

INSERT INTO OnlyIntsBeforeAndAfterP VALUES
('87251510P25'),
('323P5'), 
('12345KP19'),
('32423P'),
('P5972319'),
('1232P1/7'),
('1987621P,29/7')
GO

SELECT * FROM OnlyIntsBeforeAndAfterP
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solutions 🙂

SOLUTION 1

--

;WITH CTE AS
(
	SELECT Vals V , Vals , CHARINDEX('P',Vals,0) Ix
	FROM OnlyIntsBeforeAndAfterP
)
SELECT Vals
FROM CTE
WHERE 
TRY_CAST(SUBSTRING(V,0,Ix) AS BIGINT) IS NOT NULL AND SUBSTRING(V,0,Ix) <> '' 
AND TRY_CAST(SUBSTRING(V,Ix+1,100) AS BIGINT) IS NOT NULL AND SUBSTRING(V,Ix+1,100) <> ''

--

OUTPUT 1

--

Vals
--------------------------------------------------
87251510P25
323P5

(2 rows affected)

--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com