Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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)

--

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.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

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

Advertisements