DECLARE @i TABLE (fnumber NVARCHAR(100))INSERT INTO @i(fnumber) SELECT 'aa.bb.cc' UNION ALL SELECT 'abc.a.c' UNION ALL SELECT 'ccccc.bac.ffffff'SELECT PARSENAME(fnumber,3),PARSENAME(fnumber,2),PARSENAME(fnumber,1) FROM @i /* (无列名) (无列名) (无列名) aa bb cc abc a c ccccc bac ffffff*/
这个方法不错 但是是我没写完整 字符串可能有这种情况 1.只有一个点 2.只有2个点 3.有3个点DECLARE @i TABLE (fnumber NVARCHAR(100))INSERT INTO @i(fnumber) SELECT 'aa.bb.cc' UNION ALL SELECT 'abc.a.c' UNION ALL SELECT 'ccccc.bac.ffffff' UNION ALL SELECT 'aaa.bbb.ccc.ddd' UNION ALL SELECT 'bbb.ddd'SELECT ISNULL(PARSENAME(fnumber,4),PARSENAME(fnumber,3)), ISNULL(PARSENAME(fnumber,3),PARSENAME(fnumber,2)), ISNULL(PARSENAME(fnumber,2),PARSENAME(fnumber,1)) FROM @i --类似这样做个判断即可/* (无列名) (无列名) (无列名) aa aa bb abc abc a ccccc ccccc bac aaa bbb ccc NULL bbb bbb*/
SELECT 'aa.bb.cc'
UNION ALL
SELECT 'abc.a.c'
UNION ALL
SELECT 'ccccc.bac.ffffff'SELECT PARSENAME(fnumber,3),PARSENAME(fnumber,2),PARSENAME(fnumber,1) FROM @i
/*
(无列名) (无列名) (无列名)
aa bb cc
abc a c
ccccc bac ffffff*/
SELECT 'aa.bb.cc'
UNION ALL
SELECT 'abc.a.c'
UNION ALL
SELECT 'ccccc.bac.ffffff'
UNION ALL
SELECT 'aaa.bbb.ccc.ddd'
UNION ALL
SELECT 'bbb.ddd'SELECT ISNULL(PARSENAME(fnumber,4),PARSENAME(fnumber,3)),
ISNULL(PARSENAME(fnumber,3),PARSENAME(fnumber,2)),
ISNULL(PARSENAME(fnumber,2),PARSENAME(fnumber,1))
FROM @i --类似这样做个判断即可/*
(无列名) (无列名) (无列名)
aa aa bb
abc abc a
ccccc ccccc bac
aaa bbb ccc
NULL bbb bbb*/