CODEWITHSUNDEEP
sqlsql-serverstringt-sqlfiltering
db_noob
db_noob

Reputation: 135

String separation and column filtering to new columns

Hei, I'm quite new using SQL server and I'm looking for some help. I have to extract value 'Lab' from ParLab in TableA. I need some help with filtering from only the codes from 'Lab' that begin with 'L' and from 'Mcode' the ones that are equal to '9LL' and '2AN'. 

 TableA 

 ParLab  Mcode  Entry
 L;58    9LL    948487
 L;58    2AN    [email protected]
 L;80    9LL    938745
 L;58    3B2    563467
 T;80    2AN    [email protected]
 T;88    9LL    827120
 T;88    9LL    [email protected]
 A;7;2   2AN    928233

Then I have to join on 'Lab' from TableB on the values separated from the string ParLab that correspond to 'Lab'.

 TableB

 Lab        Customer
 58         Lynn
 80         Laurence
 88         Emmanuel

I have used this code to separate the string:

  SELECT ParLab, MCode, Entry
        ,SUBSTRING(ParLab,CHARINDEX(';', ParLab)+1, LEN(ParLab)) AS Lab

  FROM TableA

The result should contain Tel and Email in different columns assigned to 'Lab' and Customer joined to TableB on 'Lab'.

  Customer  Lab  Tel      Email
  Lynn      58   948487   [email protected]
  Laurence  80   938745   [email protected]
  Emmanuel  88   827120   [email protected]

Upvotes: 0

Views: 56

Answers (1)

Sreenu131
Sreenu131

Reputation: 2516

Try This below

DECLARE @TableA  AS TABLE ( ParLab  VARCHAR(100), Mcode VARCHAR(100),  Entry VARCHAR(100))
INSERT INTO @TableA
SELECT 'L;58' ,'9LL','948487'   UNION ALL
SELECT 'L;58' ,'2AN','[email protected]'  UNION ALL
SELECT 'L;80' ,'9LL','938745'   UNION ALL
SELECT 'L;58' ,'3B2','563467'   UNION ALL
SELECT 'T;80' ,'2AN','[email protected]'  UNION ALL
SELECT 'T;88' ,'9LL','827120'   UNION ALL
SELECT 'T;88' ,'9LL','[email protected]'  UNION ALL
SELECT 'A;7;2','2AN','928233'

DECLARE @TableB  AS TABLE ( Customer  VARCHAR(100), Lab INT)
INSERT INTO @TableB
SELECT 'Lynn'      ,58 UNION ALL
SELECT 'Laurence'  ,80 UNION ALL
SELECT 'Emmanuel'  ,88 


SELECT  b.Customer,
        b.Lab,
        MAX(CASE WHEN ISNUMERIC(Entry)=1 THEN Entry END)As Tel,
        MAX(CASE WHEN ISNUMERIC(Entry)<>1 THEN Entry END) AS Email 
FROM
(
SELECT  ParLab,
        Mcode,
        [Entry],
        CASE WHEN LEN(ParLab)-LEN(REPLACE(ParLab,';','')) = 1 
        THEN  SUBSTRING(ParLab,CHARINDEX(';', ParLab)+1,LEN(ParLab)) 
        ELSE NULL END AS Lab        
FROM @TableA 
) AS A
RIGHT JOIN @TableB B
ON a.Lab = b.Lab
GROUP BY b.Customer,b.Lab

Result

Customer    Lab Tel     Email
*******************************
Lynn        58  948487  [email protected]
Laurence    80  938745  [email protected]
Emmanuel    88  827120  [email protected]

Upvotes: 2

Related Questions