finding lower case data

I have a table with a VIN number column, I need to find all entries where th
e
VIN contains a lower case character, example '4d0131P2X4E146985'. This recor
d
should be included in the results because it contains 'd'.. What would be th
e
easiest way to write a query to return these, can I use regular expression i
n
T-SQL...?
Thanks,
DanAlien2_51,
Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the column
in question. I guess it is using a case insensitive one. Use the COLLATE
clause to tell SQL Server which collation to use during the operation and us
e
a case sensitive one. You can also use function binary_checksum.
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where binary_checksum(c1) != binary_checksum(upper(c1))
AMB
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan|||You could do this:
select VIN
from yourTable
where VIN like '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS
-- or
select VIN
from yourTable
where VIN like '%[a-z]%'
collate Latin1_General_BIN
Note that it will not work to use the range [a-z] in the first
example, because in the CS_AS collation, [a-z] contains the
uppercase letters A through Y.
Steve Kass
Drew University
http://www.stevekass.com
Alien2_51 wrote:

>I have a table with a VIN number column, I need to find all entries where t
he
>VIN contains a lower case character, example '4d0131P2X4E146985'. This reco
rd
>should be included in the results because it contains 'd'.. What would be t
he
>easiest way to write a query to return these, can I use regular expression
in
>T-SQL...?
>Thanks,
>Dan
>|||Thanks Alejandro... Just what I needed, and so simple...
DB
"Alejandro Mesa" wrote:
> Alien2_51,
> Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the colu
mn
> in question. I guess it is using a case insensitive one. Use the COLLATE
> clause to tell SQL Server which collation to use during the operation and
use
> a case sensitive one. You can also use function binary_checksum.
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where binary_checksum(c1) != binary_checksum(upper(c1))
>
> AMB
>
> "Alien2_51" wrote:
>|||Alien, I guess you might want to check this out
http://vadivel.blogspot.com/2003/12...e-searches.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan

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."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..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" <bourgon@.gmail.com> wrote in message
news:1111101450.978587.314110@.l41g2000cwc.googlegr oups.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..sysobjects a, master..sysobjects b, master..sysobjects c

Mr Tea

"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:l2w_d.814$MO6.640@.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..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" <bourgon@.gmail.com> wrote in message
> news:1111101450.978587.314110@.l41g2000cwc.googlegr oups.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_rFact.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sat, 19 Mar 2005 18:03:07 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.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).

Hi Erland,

As someone who occasionaly uses ^H in messages to sufficiently geeky
persons, I really should have known that...

Thanks for picking my nit!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Finding due date from a six character long date field

Hi All,
Here is my problem.
I have a datefield as varchar and six characters long.
The date is shown as 200102 which means the entire month of February
in 2001.
I need to find the due date based on this adding 30 days and the date
will be the last date of the month.
For ex.
if the bill date is 200102 the due date will be 03/30/2001
if the bill date is 200112 the due date will be 01/31/2002
and so on.
Could anyone please tell me how can I write the query?
I have given the DDLs below:
CREATE TABLE [dbo].[Table1] (
[ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
)
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200101')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200102')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200103')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200112')
I was trying to run the following query:
SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
INVOICENO='500004'
of course, that did not give me the right answer.
I do highly appreciate your help.
Thanks a million in advance.
Best regards,
mamunHello Magmun !
Try this:
--German Version with dot seperated
Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
left(SUPLBILLDT,4)))) from Table1
--English Version with commy separated
Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
left(SUPLBILLDT,4)))) from Table1
HTH, Jens Süßmeyer.|||How about
SELECT due_date = DATEADD
(
MONTH,
2,
CONVERT(SMALLDATETIME, bill_date + '01', 112)
)-1
FROM table
(I assume you meant 03/31/2001, since 03/30 is not the last day of March.
And also, you're not adding 30 days to 20011201 and then setting the due
date to be the last day of that month, because 20011201 + 30 = 20011231 and
the last day of that month is 12/31, not 20020131. In other words, the
description of your requirements does not match your sample output... so if
the above query doesn't do what you want, you'll need to clarify;
preferably, with actual table structure, sample data (in the form of a
functional insert statement), and desired results.)
"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0308220710.1ed91260@.posting.google.com...
> Hi All,
> Here is my problem.
> I have a datefield as varchar and six characters long.
> The date is shown as 200102 which means the entire month of February
> in 2001.
> I need to find the due date based on this adding 30 days and the date
> will be the last date of the month.
> For ex.
> if the bill date is 200102 the due date will be 03/30/2001
> if the bill date is 200112 the due date will be 01/31/2002
> and so on.
> Could anyone please tell me how can I write the query?
>
> I have given the DDLs below:
>
>
> CREATE TABLE [dbo].[Table1] (
> [ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> )
>
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200101')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200102')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200103')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200112')
> I was trying to run the following query:
> SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
> INVOICENO='500004'
> of course, that did not give me the right answer.
> I do highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> mamun|||Why not return as a datetime, then you can wrap whatever presentation you
want around it using CONVERT and style. In your method, btw, you are
assuming that all of your string concatenations will not break datetime; I'm
not sure this is a healthy assumption, since you may not want to write code
that relies on a specific language / dateformat setting, and the machine's
regional settings.
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:eM$OtKMaDHA.2668@.TK2MSFTNGP09.phx.gbl...
> Hello Magmun !
> Try this:
>
> --German Version with dot seperated
> Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
> left(SUPLBILLDT,4)))) from Table1
> --English Version with commy separated
> Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
> left(SUPLBILLDT,4)))) from Table1
>
> HTH, Jens Süßmeyer.
>|||OK, convinced :)). Didn´t thought about this solution, even we use it
(Yours, just implemented by ME) to keep track of our guarantee expiry dates
X-)).
Jens Süßmeyer.sql

Find second character in a string

Hi,
I have a string such as .75.34.100.
How do I find the position of the second comma from the right.
I been trying to use PATINDEX
DECLARE @.string varchar(30)
SET @.string = '.75.34.100.'
SELECT PATINDEX('%.', LEFT(@.string, LEN(@.string)-1))
but it gives me 0
any insight on this?
Thanks in advance
Christian"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:ej3EVBcmFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a string such as .75.34.100.
> How do I find the position of the second comma from the right.
> I been trying to use PATINDEX
> DECLARE @.string varchar(30)
> SET @.string = '.75.34.100.'
> SELECT PATINDEX('%.', LEFT(@.string, LEN(@.string)-1))
> but it gives me 0
> any insight on this?
> Thanks in advance
> Christian
>
Take a look at the REVERSE function. This will reverse your character
string. Then you can use your PATINDEX or CHARINDEX.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick,
but if I do a reverse then I need to find the position of the second comma
from the left instead of right.
So I will still be stuck in the same situation. Note, all my strings starts
and end with a comma.
--Americas .75.86.
-- Puerto Rico .75.86.17.
-- Latin America .75.86.70.
-- Dominican Republic
.75.86.70.108.
-- Haiti
.75.86.70.110.
-- South America
.75.86.70.22.
-- Uruguay
.75.86.70.22.125.
-- Peru
.75.86.70.22.19.
-- Argentina
.75.86.70.22.21.
-- Brazil
.75.86.70.22.53.
-- Chile
.75.86.70.22.69.
what I need to do is be able to sort by country name and I can do that by
getting rid of the last number in the path.
Thanks
Christian
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OWT6JGcmFHA.3608@.TK2MSFTNGP15.phx.gbl...
> "Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
> message news:ej3EVBcmFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Take a look at the REVERSE function. This will reverse your character
> string. Then you can use your PATINDEX or CHARINDEX.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:%23rf13QcmFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Thanks Rick,
> but if I do a reverse then I need to find the position of the second comma
> from the left instead of right.
> So I will still be stuck in the same situation. Note, all my strings
> starts
> and end with a comma.
> --Americas .75.86.
> -- Puerto Rico .75.86.17.
> -- Latin America .75.86.70.
> -- Dominican Republic
> .75.86.70.108.
> -- Haiti
> .75.86.70.110.
> -- South America
> .75.86.70.22.
> -- Uruguay
> .75.86.70.22.125.
> -- Peru
> .75.86.70.22.19.
> -- Argentina
> .75.86.70.22.21.
> -- Brazil
> .75.86.70.22.53.
> -- Chile
> .75.86.70.22.69.
> what I need to do is be able to sort by country name and I can do that by
> getting rid of the last number in the path.
> Thanks
> Christian
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OWT6JGcmFHA.3608@.TK2MSFTNGP15.phx.gbl...
>
Try this out...
CREATE TABLE #Foo (
Value varchar(20)
)
INSERT #Foo VALUES ('100,28,390,20,14,')
INSERT #Foo VALUES ('10,13,390,71,14,')
INSERT #Foo VALUES ('100,28,390,12,15424,')
INSERT #Foo VALUES ('100,28,390,33,26,')
INSERT #Foo VALUES ('100,28,39080,14,')
SELECT LEFT(Value, LEN(Value) - -- Get the LEFT of the Length minus
the CHARINDEX value of the second comma
CHARINDEX(',', REVERSE(Value), -- Find Second comma from the right
(CHARINDEX(',', REVERSE(Value), 1) + 1))) -- Find First comma from the
right
FROM #Foo
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||> but if I do a reverse then I need to find the position of the second comma
> from the left instead of right.
Maybe you could use fn_split() if the number of decimal points (not commas)
is constant.
http://msdn.microsoft.com/library/e...eatYourself.asp|||Try using RIGHT instead of LEFT.
SELECT PATINDEX('%.', RIGHT(@.string, LEN(@.string)-1))
Hope this helps.
TDN
"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:ej3EVBcmFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a string such as .75.34.100.
> How do I find the position of the second comma from the right.
> I been trying to use PATINDEX
> DECLARE @.string varchar(30)
> SET @.string = '.75.34.100.'
> SELECT PATINDEX('%.', LEFT(@.string, LEN(@.string)-1))
> but it gives me 0
> any insight on this?
> Thanks in advance
> Christian
>|||You can use this (adapting from another post)
e.g.
select top 8000 digit=identity(int,1,1)
into digits
from sysobjects,syscolumns
go
create function dbo.xtract(@.input varchar(8000))
returns varchar(8000)
as
begin
declare @.tb table (i int identity primary key, value sysname)
declare @.s varchar(8000)
declare @.delim char
-- desired delimiter
set @.delim=','
set @.input = @.delim+rtrim(ltrim(@.input))+@.delim
insert @.tb
select substring(@.input, n.digit+1,
charindex(@.delim,@.input,n.digit+1)-n.digit-1) value
from digits as n
where n.digit<len(@.input)
and substring(@.input,n.digit,1) = @.delim
-- pick the desired value
select @.s=value
from @.tb
where i=2
return @.s
end
go
declare @.csv varchar(8000)
set @.csv='asdga,1324,afasf afds'
select dbo.xtract(@.csv)
-oj
"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:%23rf13QcmFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Thanks Rick,
> but if I do a reverse then I need to find the position of the second comma
> from the left instead of right.
> So I will still be stuck in the same situation. Note, all my strings
> starts
> and end with a comma.
> --Americas .75.86.
> -- Puerto Rico .75.86.17.
> -- Latin America .75.86.70.
> -- Dominican Republic
> .75.86.70.108.
> -- Haiti
> .75.86.70.110.
> -- South America
> .75.86.70.22.
> -- Uruguay
> .75.86.70.22.125.
> -- Peru
> .75.86.70.22.19.
> -- Argentina
> .75.86.70.22.21.
> -- Brazil
> .75.86.70.22.53.
> -- Chile
> .75.86.70.22.69.
> what I need to do is be able to sort by country name and I can do that by
> getting rid of the last number in the path.
> Thanks
> Christian
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OWT6JGcmFHA.3608@.TK2MSFTNGP15.phx.gbl...
>|||Try:
SELECT
SUBSTRING( address, LEN( address ) - CHARINDEX( '.', REVERSE( address ),
CHARINDEX( '.', REVERSE( address ), 2 ) + 1 ) + 2, 10 )
FROM <your_table>
Replace the 'address' string with your field and put in your table for
<your_table>
Let me know how you get on.
Damien
"Christian Perthen" wrote:

> Thanks Rick,
> but if I do a reverse then I need to find the position of the second comma
> from the left instead of right.
> So I will still be stuck in the same situation. Note, all my strings start
s
> and end with a comma.
> --Americas .75.86.
> -- Puerto Rico .75.86.17.
> -- Latin America .75.86.70.
> -- Dominican Republic
> ..75.86.70.108.
> -- Haiti
> ..75.86.70.110.
> -- South America
> ..75.86.70.22.
> -- Uruguay
> ..75.86.70.22.125.
> -- Peru
> ..75.86.70.22.19.
> -- Argentina
> ..75.86.70.22.21.
> -- Brazil
> ..75.86.70.22.53.
> -- Chile
> ..75.86.70.22.69.
> what I need to do is be able to sort by country name and I can do that by
> getting rid of the last number in the path.
> Thanks
> Christian
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OWT6JGcmFHA.3608@.TK2MSFTNGP15.phx.gbl...
>
>|||Hi There,
You may try this. Little restrictive but works.
declare @.v varchar(80)
set @.v='a,jat,pat,kat'
set @.v = replace (@.v,',','.')
select charindex('.'+parsename(@.v,2),@.v)
With warm regards
Jatinder Singh

find any character in a string that is a lower case

in SQL Server 7, How can I find out if a database field has any lower case
character in the string. Thanks!you can create a temp table w/ the letters of the alphabet in it and
search that field IN #temp table.
or you could find a length of the string and then substring letter by
letter and search the ASCII values for each lowercase letter against
the substring letter.|||Thanks! What I did is to find the sum of the ASCII values for the whole
string and the sum of ASCIIs for upper(string) at the same time. If they are
not equal, there is a lower case character.
"GlennThomas5" wrote:

> you can create a temp table w/ the letters of the alphabet in it and
> search that field IN #temp table.
> or you could find a length of the string and then substring letter by
> letter and search the ASCII values for each lowercase letter against
> the substring letter.
>

find any character in a string that is a lower case

in SQL Server 7, How can I find out if a database field has any lower case
character in the string. Thanks!you can create a temp table w/ the letters of the alphabet in it and
search that field IN #temp table.
or you could find a length of the string and then substring letter by
letter and search the ASCII values for each lowercase letter against
the substring letter.|||Thanks! What I did is to find the sum of the ASCII values for the whole
string and the sum of ASCIIs for upper(string) at the same time. If they are
not equal, there is a lower case character.
"GlennThomas5" wrote:
> you can create a temp table w/ the letters of the alphabet in it and
> search that field IN #temp table.
> or you could find a length of the string and then substring letter by
> letter and search the ASCII values for each lowercase letter against
> the substring letter.
>

find any character in a string that is a lower case

in SQL Server 7, How can I find out if a database field has any lower case
character in the string. Thanks!
you can create a temp table w/ the letters of the alphabet in it and
search that field IN #temp table.
or you could find a length of the string and then substring letter by
letter and search the ASCII values for each lowercase letter against
the substring letter.
|||Thanks! What I did is to find the sum of the ASCII values for the whole
string and the sum of ASCIIs for upper(string) at the same time. If they are
not equal, there is a lower case character.
"GlennThomas5" wrote:

> you can create a temp table w/ the letters of the alphabet in it and
> search that field IN #temp table.
> or you could find a length of the string and then substring letter by
> letter and search the ASCII values for each lowercase letter against
> the substring letter.
>

find a character in string

Good morning,
In the past, with an Informix db and asp/vbscript pages, I've used the
following SQL statement:
select CustomerName, DrawNumber
from Customer
Where DrawNumber[4,4] not in ("I","M")
to find DrawNumber where the 4th character is not equal to I or M. Now that
we've switched over to a SQLServer db, I'm having some trouble getting this
statement work, or finding the equivalent. Any suggestions?
Thanks in advance!
RoseLookup how to use SUBSTRING() function in SQL Server Books Online.
Anith|||where DrawNumber not like '___[IM]%'
(3 underscores before the opening bracket)
Rose wrote:
> Good morning,
> In the past, with an Informix db and asp/vbscript pages, I've used the
> following SQL statement:
> select CustomerName, DrawNumber
> from Customer
> Where DrawNumber[4,4] not in ("I","M")
> to find DrawNumber where the 4th character is not equal to I or M. Now tha
t
> we've switched over to a SQLServer db, I'm having some trouble getting thi
s
> statement work, or finding the equivalent. Any suggestions?
> Thanks in advance!
> Rose|||Look at SUBSTRING() in BOL. It will help.
Perayu
"Rose" <Rose@.discussions.microsoft.com> wrote in message
news:BA7A20AD-3FF6-414D-96AC-8DF5C34FF75B@.microsoft.com...
> Good morning,
> In the past, with an Informix db and asp/vbscript pages, I've used the
> following SQL statement:
> select CustomerName, DrawNumber
> from Customer
> Where DrawNumber[4,4] not in ("I","M")
> to find DrawNumber where the 4th character is not equal to I or M. Now
> that
> we've switched over to a SQLServer db, I'm having some trouble getting
> this
> statement work, or finding the equivalent. Any suggestions?
> Thanks in advance!
> Rose|||Thanks Trey! It worked perfectly!
"Trey Walpole" wrote:

> where DrawNumber not like '___[IM]%'
> (3 underscores before the opening bracket)
> Rose wrote:
>