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

Vals
87251510P25
323P5
12345KP19
32423P
P5972319
1232P1/7
1987621P,29/7

Expected OUPUT

Vals
87251510P25
323P5

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) -- ```

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.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/