473,589 Members | 2,649 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Finding last "whitespace " character in a string?

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.

Jul 23 '05 #1
10 27873
"M Bourgon" wrote:
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.


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
Jul 23 '05 #2
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..sysobje cts, master..sysobje cts, master..sysobje cts

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(@St r) 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.goo glegroups.com.. .
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.

Jul 23 '05 #3
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..sysobje cts a, master..sysobje cts b, master..sysobje cts c

Mr Tea

"Lee Tudor" <mr****@ntlworl d.com> wrote in message
news:l2******** *******@newsfe2-gui.ntli.net...
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..sysobje cts, master..sysobje cts, master..sysobje cts

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(@St r) 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.goo glegroups.com.. .
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.


Jul 23 '05 #4
On 17 Mar 2005 15:17:31 -0800, M Bourgon wrote:
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?


Hi M,

Yep - use PATINDEX instead of CHARINDEX. In the example below, I search
for space and char(8) (tab) only, but it's easy to add other whitespace
characters. To test it, run the code below, uncomment the commented line
and run it again - you'll see that first the space, then the tabl is
found.

declare @a varchar(100)
set @a = 'This is a test'
-- + char(8) + 'tabbed'
select @a
declare @LastWhite int
set @LastWhite = len(@a) - patindex('%[ ' + char(8) + ']%', reverse(@a))
+ 1
select @LastWhite
select substring(@a, @LastWhite, 99)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #5
D'oh! That's exactly what I was looking for. Many thanks, everyone.

Jul 23 '05 #6
Okay, I thought that was the answer, but not quite. My problem is that
I'm looking to send back the "this is a test", even if it's technically
'this is a test ' + char(8) + char(8). Patindex will send the first
instance, which in this case would mean there's still a char(8) & a
space left. I saw some code that does something like this, where it
uses a while loop to step through the table, but I'd rather avoid that
if possible.

I'll give Lee's "table of Numbers" trick a shot next. Thanks, all.

Jul 23 '05 #7
On 18 Mar 2005 07:18:21 -0800, M Bourgon wrote:
Okay, I thought that was the answer, but not quite. My problem is that
I'm looking to send back the "this is a test", even if it's technically
'this is a test ' + char(8) + char(8). Patindex will send the first
instance, which in this case would mean there's still a char(8) & a
space left. I saw some code that does something like this, where it
uses a while loop to step through the table, but I'd rather avoid that
if possible.

I'll give Lee's "table of Numbers" trick a shot next. Thanks, all.


Hi M,

So I guess that you're not looking for the last whitespace, but for the
last non-whitespace? Very easy to do - just add one caret (^) to my
code:

declare @a varchar(100)
set @a = 'This is a test ' + char(8) + char(8)
select @a
declare @LastWhite int
set @LastWhite = len(@a) - patindex('%[^ ' + char(8) + ']%',
reverse(@a)) + 1
select @LastWhite
select substring(@a, @LastWhite, 99)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #8
Aha! Boy, am I dense.

One correction, for future generations reading this: change the len(@a)
to datalength(@a), in case all that's at the end are spaces (since it's
a varchar, it'll automatically drop spaces at the end). You could
probably change it to a char(100) as well, but this way you're
(hopefully) not using up as much memory.

Thanks again, Hugo.

Jul 23 '05 #9
Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:
Yep - use PATINDEX instead of CHARINDEX. In the example below, I search
for space and char(8) (tab) only,


The nit-picking department like to point out that char(8) is backspace. Tab
is char(9).
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2181
by: ME | last post by:
I am trying to find a prewritten method for converting a string like this:"Hello World" to this "Hello\sWorld". I plan to use it to build a regular expression. Specifically I am looking for a method that can convert not just space (\s) but also any character that needs to be escaped in a given string. Thanks, Matt
8
2723
by: bonj | last post by:
hello I hope somebody can help me get my head around this area of 'stream' programming... I know that streams are very fashionable nowadays so hopefully there'll be lots of replies. ;-) Basically I have an operation which the input and output for are streams - a function which receives a certain 'chunk' of data each time it runs, and it runs many times until it has received all the data, in a similar way to a socket. What the...
7
2842
by: Robin Haswell | last post by:
Hey guys. This should just be a quickie: I can't figure out how to convert r"\x2019" to an int - could someone give me a hand please? Cheers -Rob
13
1914
by: Jim | last post by:
Could somebody tell me why I need the "elif char == '\n'" in the following code? This is required in order the pick up lines with just spaces in them. Why doesn't the "else:" statement pick this up? OLD_INDENT = 5 # spaces NEW_INDENT = 4 # spaces print 'Reindent.py:'
1
6466
by: laredotornado | last post by:
Hi, I'm using PHP 4.4.4 on Apache 2 on Fedora Core 5. PHP was installed using Apache's apxs and the php library was installed to /usr/local/php. However, when I set my "error_reporting" setting to be "E_ALL", notices are still not getting reported. The perms on my file are 664, with owner root and group root. The php.ini file is located at /usr/local/lib/php/php.ini. Any ideas why the setting does not seem to be having an effect? ...
7
11654
by: tah | last post by:
Hey, Can someone please clarify, confirm, or set me straight on my understanding of a standalone="yes" attribute in the xml version element? I assume it means that the xml document containing it is standalone, and does not refer to any external document to define types. In other words, it doesn't use an external dtd to validate any types - everything used would be defined within the doc itself. In other words, you would never see an xml...
21
7818
by: comp.lang.tcl | last post by:
set php {<? print_r("Hello World"); ?>} puts $php; # PRINTS OUT <? print_r("Hello World"); ?> puts When I try this within TCL I get the following error:
8
3609
by: Ulysse | last post by:
Hello, I need to clean the string like this : string = """ bonne mentalit&eacute; mec!:) \n <br>bon pour info moi je suis un serial posteur arceleur dictateur ^^* \n <br>mais pour avoir des resultats probant il faut pas faire les mariolles, comme le &quot;fondateur&quot; de bvs
13
1932
by: Boris | last post by:
Can anyone tell me if Opera 9.5 is behaving correctly when wrapping the word C++, eg: C+ + Opera 9.2 didn't wrap C++. For those who use Opera 9.5 there is a test case at http://www.highscore.de/browsertest/cpp.html (try different window sizes until Opera 9.5 wraps C++).
0
7931
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7865
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8360
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
6637
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
3849
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3889
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2374
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1461
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1198
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.