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. 10 27864
"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
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.
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.
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)
D'oh! That's exactly what I was looking for. Many thanks, everyone.
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.
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)
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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...
|
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
|
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:'
|
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...
| |
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 -...
|
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:
|
by: Ulysse |
last post by:
Hello,
I need to clean the string like this :
string =
"""
bonne mentalité 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 "fondateur" de bvs
|
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++).
|
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...
|
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...
| |
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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
| |