sql 截取字段中空格前的字符

用charindex和substring可以实现截取
顺便说一句,如果你得空格不是固定的,你就得定义一个字符串为"a,b,c,d,e,f.........z"
根据空格个数,获取列明,拼接语句.

用SQL内置函数LEFT()和CHARINDEX()
CHARINDEX(' ',目标字段) -空格在目标字段的位置-（返回数字n）
LEFT(目标字段,CHARINDEX(' ',目标字段)-1) -截取目标字段，从左边开始的n-1各字符
如果目标字段有不存在空格的情况，可以用IIF() 条件：n>0

兄弟思路是这样的主要用的函数

PATINDEX('% %',CC)查找空格所在的位置，然后结合RIGHT 和LEFT函数；层层来取。

具体语句如下：（你的的时候将字段CC换成你的字段，CYG表换成你的表）

SELECT LEFT(CC,PATINDEX('% %',CC)-1) AS A,

LEFT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-1) AS B,

LEFT(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))),

PATINDEX('% %',RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))-1) AS C,

LEFT(RIGHT(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))),LEN(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))-

PATINDEX('% %',RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))),PATINDEX('% %',RIGHT(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))),LEN(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))-

PATINDEX('% %',RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))))-1) AS D,

RIGHT(RIGHT(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))),LEN(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))-

PATINDEX('% %',RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))),

LEN(RIGHT(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))),LEN(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))-

PATINDEX('% %',RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))))-

PATINDEX('% %',RIGHT(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))),LEN(RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))-

PATINDEX('% %',RIGHT(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)),LEN(RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))-

PATINDEX('% %',RIGHT(CC,LEN(CC)-PATINDEX('% %',CC)))))))) AS E

FROM CYG WHERE NOL IN('0001','0002')

列不就是字段吗