22,210
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
info NVARCHAR(30)
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES ('84239556\955\300');
INSERT INTO t VALUES ('8423952\9');
INSERT INTO t VALUES ('12345');
SELECT
*
,CASE WHEN CHARINDEX('\',info)>0 THEN LEFT(info,CHARINDEX('\',info)-1)
ELSE info
END AS result
FROM t;
/*
id info result
----------- ------------------------------ ------------------------------
1 84239556\955\300 84239556
2 8423952\9 8423952
3 12345 12345
*/
select IF((LOCATE("\\", path) - 1) >= 0, SUBSTR(path, 1, LOCATE("\\", path) - 1), path) path from table_test