根据是否为连字符分隔，将1个字段解析为2或1 [英] Parsing 1 field into 2 or 1 based on whether it is hyphen delimited or not

问题描述

我有一个实验室结果字段，根据运行的实验室测试显示为5.6-6.3或正面。我被要求将连字符结果拆分为2个字段HighRange，LowRange。如果结果没有连字符，他们希望结果在第3个字段= ALPHA。我可以使用以下代码中的SUBSTRING分隔取消结果：

I have a field of lab results that either appear like '5.6-6.3' or like 'Positive' based on the lab test that is run. I've been asked to split the hyphened results into 2 fields HighRange, LowRange. Where the result is not hyphenated, they want the result in a 3rd filed = ALPHA. I can get the hyphened results separated with a SUBSTRING in the following code:

select top 100 RESULT_NORMAL_RANGE,
	SUBSTRING(RESULT_NORMAL_RANGE,1,CHARINDEX('-',RESULT_NORMAL_RANGE) -1) LowRange,--minus one to remove the dash
    SUBSTRING(RESULT_NORMAL_RANGE,CHARINDEX('-',RESULT_NORMAL_RANGE) +1 ,LEN(RESULT_NORMAL_RANGE)) HighRange
      	from HPSITE.LAB_RESULT
	WHERE RESULT_NORMAL_RANGE LIKE '%-%'

我正在努力的是当结果未连字时，填充到第3个字段'ALPHA'。我认为CASE声明可能有效。请指教！



感谢您的帮助。



我的尝试：



我试过搜索和谷歌搜索解决方案，但还没有拿出我需要的东西。

What I'm struggling with is when the result is NOT hyphenated, to populate to a 3rd field 'ALPHA'. I'm thinking a CASE statement might work. Please advise!

Thanks for your help.

What I have tried:

I have tried searching and googling a solution, but have not come up with what I need.

推荐答案

不确定我是否正确理解了问题，但要分割值，请考虑以下示例

Not sure if I understand the question correctly, but to split the values, consider the following example

CREATE TABLE #test1 (
  SomeData varchar(100)
);

INSERT INTO #test1 VALUES 
('5.6-6.3'),
('7.8'),
('1.2-3.4'),
('-9.9'),
('0.0-'),
('> 60');

SELECT 
   SomeData,
   CHARINDEX( '-', SomeData, 0) AS Position,
   CASE
      WHEN CHARINDEX( '-', SomeData, 0) = 0 
	     THEN SomeData
   END AS Alpha,
   CASE
      WHEN CHARINDEX( '-', SomeData, 0) > 1 
	     THEN SUBSTRING(SomeData, 0,CHARINDEX( '-', SomeData, 0))
   END AS Low,
   CASE
      WHEN CHARINDEX( '-', SomeData, 0) > 0 
	  AND CHARINDEX( '-', SomeData, 0) < LEN(SomeData) 
	     THEN SUBSTRING(SomeData, CHARINDEX( '-', SomeData, 0) + 1, 9999)
   END AS High
FROM #test1;

结果将是

The result would be

SomeData  Position  Alpha  Low   High
--------  --------  -----  ----  ----
5.6-6.3   4         NULL   5.6   6.3
7.8       0         7.8    NULL  NULL
1.2-3.4   4         NULL   1.2   3.4
-9.9      1         NULL   NULL  9.9
0.0-      4         NULL   0.0   NULL
> 60      0         > 60   NULL  NULL

附加：

--------

在高测试中添加了 - 存在检查

ADDITION:
--------
Added a check for - existence in the high test

这篇关于根据是否为连字符分隔，将1个字段解析为2或1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！