Question

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

表A 列a
字段为 1234 2345 12345 3456 23456
查询显示为 a b c d e
1234 2345 12345 3456 23456
用取空格的语句实现

Answer 1

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

Answer 2

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

Answer 3

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

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')

Answer 4

列不就是字段吗

追问

列是字段 列名为a

追答

题目没看懂啊，bcde也是列？1234是数据？要什么样的效果

追问

恩 小写字母是列名 数字是数据

追答

如果数据在数据库表里，那就直接查啊select a,b,c,d,e from A

追问

反了 是1个列分成多个列

追答

一个列a有5条数据，要将他们分成五个列是吧，可以用case...when...then...end语句
要增加一个列，比如id
select max(case id when 1 then a end),
max(case id when 2 then a end),
max(case id when 3 then a end),
max(case id when 4 then a end),
max(case id when 5 then a end)
from A

追问

case when 这个我知道
这个条件是用空格来截取 截取空格前后
因为数据中间的数字 有些数据是3位数 有些数据是4位数 也有5位数的
我是新手 谢谢指导下