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

Filters on the data

Hi guys,

I wanted to ask you for help as I am struggling with it second evening
already...
I have got tables DEVICES and PARTS.
One device can consist of multiple parts.

But...

I have also another table - FILTERS (id int, type int, is_not int,
phrase varchar(40))
where:id - just id,
type - filter type - can be 1 - for devices and 2 for parts,
is_not - says if the phrase has to be in a description (0) or must not
be there (1)
phrase - word to found in the description

My trouble is when I want to apply three filters at once:
1. Find devices with description containing PHRASE
2. Find parts with description containing PHRASE
3. Find devices with description NOT containing PHRASE

Query selecting parts and devices is like:

SELECT device.id, part.id
FROM DEVICE JOIN PARTS
WHERE ...

What I did is:

SELECT device_id, part_id FROM (
SELECT device_id, part_id FROM (
SELECT device_id, part_id FROM (
QUERY
) a
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=1)
OR EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=1 AND
device_desc LIKE '%' + phrase + '%')
) b
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=2)
OR EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=2 AND
part_desc LIKE '%' + phrase + '%')
) c
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not>0 AND type=1)
OR NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not>0 AND type=1 AND
device_desc LIKE '%' + phrase + '%')

It works, but very slow. In DEVICES tables is 2 milion rows and in
PARTS is 3 millions.

I turned SET STATISTICS IO ON, and they show that FILTERS are being
asked veeery often.

It must be more efficient way to acheve this but I must be blind.

Thanks fo any advices,
KucolOn 27 Oct 2006 14:47:17 -0700, kucol wrote:

Quote:

Originally Posted by

>Hi guys,
>
>I wanted to ask you for help as I am struggling with it second evening
>already...

(snip)

Hi Kucol,

I noticed that you have posted the same message to
microsoft.public.sqlserver.programming as well. Please do not multipost
in the future - had I not checked the other groups first, I might now
have spent time to duplicate an answer you've already gotten elsewhere.
I prefer to spend my time answering questions that have not been
answered yet.

I'll keep an eye on the discussion in .programming and chime in if I
feel I have anything new to add.

--
Hugo Kornelis, SQL Server MVP