SQL Server 选择数据到一个空格？

gcxthw6b 于 2022-12-10 发布在其他

关注(0)|答案(5)|浏览(155)

I have an MSSQL database field that looks like the examples below:

u129  james
u300  chris
u300a jim
u202  jane
u5    brian
u5z   brian2

Is there a way to select the first set of characters? Basically select all the characters up until the first line space?
I tried messing around with LEFT, RIGHT, LEN, but couldn't figure out a way to do it with variable string lengths like in my example.
Thanks!

sql-server

来源：https://stackoverflow.com/questions/13481084/select-data-up-to-a-space

5条答案

按热度按时间

svgewumm1#

You can use a combiation of LEFT and CHARINDEX to find the index of the first space, and then grab everything to the left of that.

SELECT LEFT(YourColumn, charindex(' ', YourColumn) - 1)

And in case any of your columns don't have a space in them:

SELECT LEFT(YourColumn, CASE WHEN charindex(' ', YourColumn) = 0 THEN 
    LEN(YourColumn) ELSE charindex(' ', YourColumn) - 1 END)

赞(0）回复(0）举报 2022-12-10

8yparm6h2#

select left(col, charindex(' ', col) - 1)

赞(0）回复(0）举报 2022-12-10

yxyvkwin3#

If the first column is always the same size (including the spaces), then you can just take those characters (via LEFT ) and clean up the spaces (with RTRIM ):

SELECT RTRIM(LEFT(YourColumn, YourColumnSize))

Alternatively, you can extract the second (or third, etc.) column (using SUBSTRING ):

SELECT RTRIM(SUBSTRING(YourColumn, PreviousColumnSizes, YourColumnSize))

One benefit of this approach (especially if YourColumn is the result of a computation) is that YourColumn is only specified once.

赞(0）回复(0）举报 2022-12-10

pqwbnv8z4#

如果您有时不想使用CASE语句而没有空格，则可以使用另一种方法

select REVERSE(RIGHT(REVERSE(YourColumn), LEN(YourColumn) - CHARINDEX(' ', REVERSE(YourColumn))))

这在SQL Server中是有效的，根据我的搜索，MySQL也有同样的功能

赞(0）回复(0）举报 2022-12-10

2mbi3lxu5#

如果缺少空格，可以添加一个;）
SELECT LEFT（'您的文本或列'，字符索引（"，'您的文本或列'+' '）- 1）

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 选择数据到一个空格？

5条答案

相关问题

热门标签

最新问答