473,701 Members | 2,763 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 27895
"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
2192
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
2731
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
2846
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
1926
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
6496
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
11661
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
7843
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
3611
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
1940
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
8738
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
8649
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,...
1
8978
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8936
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7829
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
5905
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4411
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...
2
2399
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2036
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.