字符串分离和对新列的列过滤 [英] String separation and column filtering to new columns

问题描述

嘿，我对使用SQL Server还是很陌生，我正在寻找帮助.我必须从TableA中的ParLab提取值"Lab".我需要一些帮助，以仅从"Lab"中以"L"开头的代码以及从"Mcode"中等于"9LL"和"2AN"的代码进行过滤.

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    [e-mail]
 L;80    9LL    938745
 L;58    3B2    563467
 T;80    2AN    [e-mail]
 T;88    9LL    827120
 T;88    9LL    [e-mail]
 A;7;2   2AN    928233

然后我必须从TableB的'Lab'上加入与字符串'LabLab'相对应的，与'Lab'相对应的值.

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 

结果应在分配给实验室"的不同列中包含电话"和电子邮件"，并且客户应加入实验室"中的TableB.

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   [e-mail]
  Laurence  80   938745   [e-mail]
  Emmanuel  88   827120   [e-mail]

推荐答案

在下面尝试一下

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','[e-mail]'  UNION ALL
SELECT 'L;80' ,'9LL','938745'   UNION ALL
SELECT 'L;58' ,'3B2','563467'   UNION ALL
SELECT 'T;80' ,'2AN','[e-mail]'  UNION ALL
SELECT 'T;88' ,'9LL','827120'   UNION ALL
SELECT 'T;88' ,'9LL','[e-mail]'  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

结果

Customer    Lab Tel     Email
*******************************
Lynn        58  948487  [e-mail]
Laurence    80  938745  [e-mail]
Emmanuel    88  827120  [e-mail]

这篇关于字符串分离和对新列的列过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！