SQL Puzzle | Extract Integer Present at the start of the string in a column

In this puzzle In the puzzle we have to extract the integer value present at the start of the string in a column. Please check the sample input and expected output.

Sample Input

Expected Output

Script

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

--

CREATE TABLE GetIntegers
(
	 Id INT
	,VALS VARCHAR(100)
)
GO

INSERT INTO GetIntegers VALUES
 (1,'P1')
,(2,'1 - Hero')
,(3,'2 - Ramesh')
,(4,'3 - KrishnaKANT')
,(5,'21 - Avtaar')
,(6,'5Laila')
,(7,'6  MMT')
,(8,'7#7#')
,(9,'#')
,(10,'8')
,(11,'98')
,(12,'111')
,(13,NULL)
GO

--

Rules/Restrictions

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

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

Solution – 1

--

SELECT * , SUBSTRING(Vals, 0, PATINDEX('%[^0-9]%', VALS + '+') ) Integers
FROM GetIntegers

--

Output

--

/*------------------------
SELECT * , SUBSTRING(Vals, 0, PATINDEX('%[^0-9]%', VALS + '+') ) Integers
FROM GetIntegers
------------------------*/                     
Id          VALS                  Integers     
----------- --------------------- ------------ 
1           P1                                 
2           1 - Hero              1            
3           2 - Ramesh            2            
4           3 - KrishnaKANT       3            
5           21 - Avtaar           21           
6           5Laila                5            
7           6  MMT                6            
8           7#7#                  7            
9           #                                  
10          8                     8            
11          98                    98           
12          111                   111          
13          NULL                  NULL         
                                               
(13 row(s) affected)                           
                                               
                                                                                                  
                                             
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | The Quantity SUM Sales Puzzle

In this puzzle In the puzzle we have to change the SUM of sales amount column for type = ‘j’. First get the quantity for column for type = ‘j’ and then get sum of all sales for the quantity for type j. Please check the sample input and expected output.

Sample Input

SumQuantity Table

Expected Output

Script

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

--

USE [master]
GO
/****** Object:  Table [dbo].[SumQuantity]    Script Date: 12/7/2017 5:29:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SumQuantity](
	[Quantity] [int] NULL,
	[product] [varchar](5) NULL,
	[desction] [varchar](15) NULL,
	[type] [varchar](1) NULL,
	[saleamount] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'xyz', N'product1', NULL, 100)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'mnd', N'product2', NULL, 200)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'dnd', N'product3', NULL, 563)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (14, N'dioa', N'product4', NULL, 456)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'xxx', N'product5', N'J', 144)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (1, N'ddd', N'product6', NULL, 226)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'dqi', N'product7', NULL, 144)
GO

--

Rules/Restrictions

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

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

Solution – 1

--


SELECT r.Quantity,r.product,r.desction,r.type,CASE WHEN r.type = 'j' THEN g.saleamount1 ELSE r.saleamount END saleamount FROM 
(
	SELECT * , CASE WHEN [type] = 'J' THEN Quantity END st
	FROM SumQuantity	
)r
CROSS APPLY
(
	SELECT SUM(saleamount) saleamount1
	FROM SumQuantity s 
	WHERE s.Quantity = r.st
)g

--

Output

--

/*------------------------

SELECT r.Quantity,r.product,r.desction,r.type,CASE WHEN r.type = 'j' THEN g.saleamount1 ELSE r.saleamount END saleamount FROM 
(
	SELECT * , CASE WHEN [type] = 'J' THEN Quantity END st
	FROM SumQuantity	
)r
CROSS APPLY
(
	SELECT SUM(saleamount) saleamount1
	FROM SumQuantity s 
	WHERE s.Quantity = r.st
)g
------------------------*/
Quantity    product desction        type saleamount
----------- ------- --------------- ---- -----------
315         xyz     product1        NULL 100
315         mnd     product2        NULL 200
315         dnd     product3        NULL 563
14          dioa    product4        NULL 456
315         xxx     product5        J    1151
1           ddd     product6        NULL 226
315         dqi     product7        NULL 144

(7 row(s) affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com