数据库字段值有多种情况，小弟我需要取特定字符之前的字符

数据库字段值有多种情况，我需要取特定字符之前的字符
如下sql语句

SQL code

select ProductName，EncapsulationType from WorkOrder

可以查询如下信息：
OM011 OC
GC012 UT
GC011 UT1
GC013 UT1_GC013
GC015 UT1_GC015
我需要取字符“_”之前的数据，sql预计应该怎么写

更改后的查询结果如下：
OM011 OC
GC012 UT
GC011 UT1
GC013 UT1
GC015 UT1

[解决办法]

SQL code

CREATE TABLE t1(    col VARCHAR(50))INSERT INTO t1SELECT 'OM011 OC' UNION ALLSELECT 'GC012 UT' UNION ALLSELECT 'GC011 UT1' UNION ALLSELECT 'GC013 UT1_GC013' UNION ALLSELECT 'GC015 UT1_GC015'SELECT * FROM t1SELECT CASE WHEN CHARINDEX('_',col)>0 THEN LEFT(col,CHARINDEX('_',col)-1) ELSE col ENDFROM t1----------------------(无列名)OM011 OCGC012 UTGC011 UT1GC013 UT1GC015 UT1
[解决办法]
SQL code
WITH WorkOrder  (ProductName,EncapsulationType) AS( SELECT 'OM011', 'OC' UNION ALL  SELECT 'GC012', 'UT' UNION ALL  SELECT 'GC011', 'UT1' UNION ALL  SELECT 'GC013', 'UT1_GC013' UNION ALL  SELECT 'GC015', 'UT1_GC015' ) select ProductName,CASE WHEN CHARINDEX('_',EncapsulationType,0)=0 THEN EncapsulationType  WHEN CHARINDEX('_',EncapsulationType,0)>0 THEN SUBSTRING(EncapsulationType,0,4) END EncapsulationType from WorkOrder /* ProductName EncapsulationType ----------- ----------------- OM011       OC GC012       UT GC011       UT1 GC013       UT1 GC015       UT1  (5 行受影响)   */
[解决办法]
探讨

不行哎

引用:
SQL code
select ProductName,stuff(EncapsulationType,patindex('_',EncapsulationType),len(EncapsulationType),'')EncapsulationType from WorkOrder