寻找最后的“空白”字符串中的字符？ [英] Finding last "whitespace" character in a string?

问题描述

我正在试图找出如何在

varchar字符串中找到最后一个空格字符。更复杂的是，它不仅仅是我想要的空间，而是某些ascii字符（否则，显然，只是

使用LEN）。我最初的想法是REVERSE它，找到位置

（使用CHARINDEX）查找每个字符（所以，多个

查询），然后从LEN中减去它字符串。


我遇到的问题是我们正在寻找大约十几种不同的b $ b字符。有什么建议？我的想法是

（这听起来很傻，所以必须有更好的方法）将每个CHARINDEX的结果转换成表格，然后找到MAX的表格和

使用它。但是，就像我说的那样，这听起来很愚蠢。我不认为我可以做一个

[^ 0-9A-Z]，因为有非字母数字字符，我们要找b
。


非常感谢。

I''m trying to figure out how to find the last whitespace character in a
varchar string. To complicate things, it''s not just spaces that I''m
looking for, but certain ascii characters (otherwise, obviously, just
use LEN). My initial thought was to REVERSE it, find the location
(using CHARINDEX) looking for each of those characters (so, multiple
queries), then subtract that from the LEN of the string.

The problem I''m running into is that there are about a dozen different
characters we''re looking for. Any suggestions? My thought was to
(this sounds silly, so there''s gotta be a better way) dump the results
from each CHARINDEX into a table, then find the MAX of the table and
use that. But, like I said, it sounds silly. I don''t think I can do a
[^0-9A-Z] either, since there are non-Alphanumeric characters we''re
looking for.

Many thanks.

推荐答案

" M Bourgon"写道：

"M Bourgon" wrote:

我正在试图找出如何在
varchar字符串中找到最后一个空白字符。更复杂的是，它不仅仅是我要寻找的空间，而是某些ascii字符（否则显然只是使用LEN）。我最初的想法是反转它，找到位置
（使用CHARINDEX）查找每个字符（所以，多个
查询），然后从字符串的LEN中减去它。

我遇到的问题是我们正在寻找大约十几个不同的角色。有什么建议？我的想法是
（这听起来很傻，所以有一个更好的方法）将每个CHARINDEX的结果转储到一个表中，然后找到表中的MAX和
用那个。但是，就像我说的那样，这听起来很愚蠢。我不认为我可以做一个
[^ 0-9A-Z]，因为我们正在寻找非字母数字字符。

非常感谢。

I''m trying to figure out how to find the last whitespace character in a
varchar string. To complicate things, it''s not just spaces that I''m
looking for, but certain ascii characters (otherwise, obviously, just
use LEN). My initial thought was to REVERSE it, find the location
(using CHARINDEX) looking for each of those characters (so, multiple
queries), then subtract that from the LEN of the string.

The problem I''m running into is that there are about a dozen different
characters we''re looking for. Any suggestions? My thought was to
(this sounds silly, so there''s gotta be a better way) dump the results
from each CHARINDEX into a table, then find the MAX of the table and
use that. But, like I said, it sounds silly. I don''t think I can do a
[^0-9A-Z] either, since there are non-Alphanumeric characters we''re
looking for.

Many thanks.

为什么不使用LIKE但是使用CHAR（）在变量中构建模式？


声明@t table（c varchar（50））


insert @t values（''not this one''）

insert @t values（''或this一个''）


插入@t值（''

甚至不是这个''）


插入@t值（''只有这一个

''）


声明@crit varchar（50）

set @ crit =''％[''+ CHAR（13）+ CHAR（10）+'']''


select * from @t where c like @crit


Craig

Why not use LIKE but build the pattern in a variable using CHAR()?

declare @t table (c varchar(50))

insert @t values (''not this one'')
insert @t values (''or this one'')

insert @t values (''
not even this one'')

insert @t values (''only this one
'')

declare @crit varchar(50)
set @crit = ''%['' + CHAR(13) + CHAR(10) + '']''

select * from @t where c like @crit

Craig

旧的数字表技巧怎么样？


首先创建一个表数字足够大，可以处理你正在处理的字符串的长度，我会在这里做8000但是它可能更多

（你只需要这样做一次）


SELECT TOP 8000 Number = IDENTITY（int，1,1）

INTO数字

来自master..sysobjects，master..sysobjects，master..sysobjects


现在你拥有了数字表，你可以用它来索引你的

字符串并寻找空白：


SELECT Top 1 Number

FROM Numbers

WHERE Number< = Len（@Str）AND Substring（@Str，Number，1）IN（char（32），

char（13）， char（8））

ORDER BY Number DESC


''从表中得到数字小于或等于
的第一个数字
字符串的长度和数字位置的字符在

给定的空格字符集中，从最高数字开始''


为获得最佳性能，请在数字表上创建聚簇索引。


您也可以使用REVERSE和PATINDEX通过编码所有

字符串的空白字符''％[''+ char（8）+ char（10）+ char（32）+'']％''，虽然我'$

从来没有尝试过使用''[]''和patindex一起使用它并不是那么有趣:)


茶先生


" M Bourgon" <博***** @ gmail.com>在消息中写道

news：11 ********************** @ l41g2000cwc.googlegr oups.com ...

How about the old table of numbers trick?

first you create a table of numbers big enough to handle the length of
string you are dealing with, I''ll do 8000 in this case but it could be more
(You only need to do this the once)

SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM master..sysobjects, master..sysobjects, master..sysobjects

Now that you have your table of numbers, you can use it to index into your
string and look for the whitespace:

SELECT Top 1 Number
FROM Numbers
WHERE Number<=Len(@Str) AND Substring(@Str, Number, 1) IN (char(32),
char(13), char(8))
ORDER BY Number DESC

''Get the first number from the table where the number is less or equal to
the length of the string and the character at the numbers position is in a
given set of whitespace characters, starting at the highest number''

For optimum performance create a clustered index on the table of numbers.

You may also be able to use REVERSE and PATINDEX by encoding a string of all
the whitespace characters ''%[''+char(8)+char(10)+char(32)+'']%'', although I''ve
never tried using ''[]'' with patindex and its not nearly as interesting :)

Mr Tea

"M Bourgon" <bo*****@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

我正在试图弄清楚如何在
varchar字符串中找到最后一个空白字符。更复杂的是，它不仅仅是我要寻找的空间，而是某些ascii字符（否则显然只是使用LEN）。我最初的想法是反转它，找到位置
（使用CHARINDEX）查找每个字符（所以，多个
查询），然后从字符串的LEN中减去它。

我遇到的问题是我们正在寻找大约十几个不同的角色。有什么建议？我的想法是
（这听起来很傻，所以有一个更好的方法）将每个CHARINDEX的结果转储到一个表中，然后找到表中的MAX和
用那个。但是，就像我说的那样，这听起来很愚蠢。我不认为我可以做一个
[^ 0-9A-Z]，因为我们正在寻找非字母数字字符。

非常感谢。

I''m trying to figure out how to find the last whitespace character in a
varchar string. To complicate things, it''s not just spaces that I''m
looking for, but certain ascii characters (otherwise, obviously, just
use LEN). My initial thought was to REVERSE it, find the location
(using CHARINDEX) looking for each of those characters (so, multiple
queries), then subtract that from the LEN of the string.

The problem I''m running into is that there are about a dozen different
characters we''re looking for. Any suggestions? My thought was to
(this sounds silly, so there''s gotta be a better way) dump the results
from each CHARINDEX into a table, then find the MAX of the table and
use that. But, like I said, it sounds silly. I don''t think I can do a
[^0-9A-Z] either, since there are non-Alphanumeric characters we''re
looking for.

Many thanks.

糟糕，

如果您没有访问权限，请不要忘记在交叉联接中对表进行别名/>
到sysobjects你可以使用任何具有相当数量记录的表。


master..sysobjects a，master..sysobjects b，master..sysobjects c


茶先生


Lee Tudor <先生**** @ ntlworld.com>在留言中写道

新闻：l2 *************** @ newsfe2-gui.ntli.net ...

Oops,
dont forget to alias the tables in the cross join, if you dont have access
to sysobjects you can use any table with a decent amount of records.

master..sysobjects a, master..sysobjects b, master..sysobjects c

Mr Tea

"Lee Tudor" <mr****@ntlworld.com> wrote in message
news:l2***************@newsfe2-gui.ntli.net...

怎么样旧的数字表技巧？

首先你创建一个数字表，大到足以处理你正在处理的字符串的长度，在这种情况下我会做8000但是它可能更多（你只需要做一次）

SELECT TOP 8000 Number = IDENTITY（int，1,1）
INTO号码
FROM master ..sysobjects，master..sysobjects，master..sysobjects

现在你有了你的数字表，你可以用它来索引你的
字符串并寻找空白：< SELECT />
SELECT Top 1 Number
FROM Numbers
WHERE Number< = Len（@Str）AND Substring（@Str，Number，1）IN（char（32）， char（13），char（8））
ORDER BY Number DESC

''从表中获取数字小于或等于
长度的第一个数字字符串和数字位置的字符在
给出一组空白字符，从最高编号开始''
为了获得最佳性能，请在数字表上创建聚簇索引。

您也可以使用REVERSE和PATINDEX通过编码一串
所有空格字符''％[''+ char（8）+ char（10）+ char（32）+'']％''，
虽然我从来没有尝试过用''[]''和patindex一起使用它并不是很有趣。

茶先生

M Bourgon <博***** @ gmail.com>在消息中写道
新闻：11 ********************** @ l41g2000cwc.googlegr oups.com ...

How about the old table of numbers trick?

first you create a table of numbers big enough to handle the length of
string you are dealing with, I''ll do 8000 in this case but it could be
more (You only need to do this the once)

SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM master..sysobjects, master..sysobjects, master..sysobjects

Now that you have your table of numbers, you can use it to index into your
string and look for the whitespace:

SELECT Top 1 Number
FROM Numbers
WHERE Number<=Len(@Str) AND Substring(@Str, Number, 1) IN (char(32),
char(13), char(8))
ORDER BY Number DESC

''Get the first number from the table where the number is less or equal to
the length of the string and the character at the numbers position is in a
given set of whitespace characters, starting at the highest number''

For optimum performance create a clustered index on the table of numbers.

You may also be able to use REVERSE and PATINDEX by encoding a string of
all the whitespace characters ''%[''+char(8)+char(10)+char(32)+'']%'',
although I''ve never tried using ''[]'' with patindex and its not nearly as
interesting :)

Mr Tea

"M Bourgon" <bo*****@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

我正在试图找出如何在
varchar字符串中找到最后一个空白字符。更复杂的是，它不仅仅是我要寻找的空间，而是某些ascii字符（否则显然只是使用LEN）。我最初的想法是反转它，找到位置
（使用CHARINDEX）查找每个字符（所以，多个
查询），然后从字符串的LEN中减去它。

我遇到的问题是我们正在寻找大约十几个不同的角色。有什么建议？我的想法是
（这听起来很傻，所以有一个更好的方法）将每个CHARINDEX的结果转储到一个表中，然后找到表中的MAX和
用那个。但是，就像我说的那样，这听起来很愚蠢。我不认为我可以做一个
[^ 0-9A-Z]，因为我们正在寻找非字母数字字符。

非常感谢。

I''m trying to figure out how to find the last whitespace character in a
varchar string. To complicate things, it''s not just spaces that I''m
looking for, but certain ascii characters (otherwise, obviously, just
use LEN). My initial thought was to REVERSE it, find the location
(using CHARINDEX) looking for each of those characters (so, multiple
queries), then subtract that from the LEN of the string.

The problem I''m running into is that there are about a dozen different
characters we''re looking for. Any suggestions? My thought was to
(this sounds silly, so there''s gotta be a better way) dump the results
from each CHARINDEX into a table, then find the MAX of the table and
use that. But, like I said, it sounds silly. I don''t think I can do a
[^0-9A-Z] either, since there are non-Alphanumeric characters we''re
looking for.

Many thanks.

这篇关于寻找最后的“空白”字符串中的字符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！