find? instr? indexof?

is there a sql keyword for find or instr?
i have a field i wish to make into two and i need the position of a string "-" in the field so i can do a select right and copy that data to a new colmCHARINDEX
Returns the starting position of the specified expression in a character string.

Syntax
CHARINDEX ( expression1 , expression2 [ , start_location ] )sql

Find SQL Instances on Network

Basic question. I'm new to my position. Lots of SQL on the network but no
documentation. How can I identify where SQL server is running and what
version is there?
Found this article on the net
http://www.databasejournal.com/featu...le.php/3397981 that is
great but only addresses one WAN Segment. HAve about 30 segements to review
and was hoping for one solution to identify all the instances at one time.
Thanks
Jody Howard
"JHoward" <jodyhow(removeme)@.hotmail.com> wrote in message
news:156905AB-BF17-4F62-8A55-1F2443AA4C6D@.microsoft.com...
> Basic question. I'm new to my position. Lots of SQL on the network but
no
> documentation. How can I identify where SQL server is running and what
> version is there?
> Found this article on the net
> http://www.databasejournal.com/featu...le.php/3397981 that is
> great but only addresses one WAN Segment. HAve about 30 segements to
review
> and was hoping for one solution to identify all the instances at one time.
Covered this on another post...
Steve

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 how an item ranks in a table?

Is it possible to find the position of an item without retrieving all the records in the table?

I have a products table, and want to find out how its sales are doing within a particular category.

My table consists of the following columns of interest:

ProductID, CategoryID, ItemsSold.

So, how would I turn the following query into an (not sure if the terminology is correct) aggregate query?

SELECT *FROM dbo.ProductsWHERE CategoryID = 10ORDER BY ItemsSold

Also, is it possible to include the SUM() of the items (in the same category) in the aggregate function or would I need to perform a separate query?

Any help would be very much appreciated.

Thanks.

The only way I can see of doing this is by creating a store procedure that returns the required values. Note that the only part I am not sure about is if the insert will work with an ORDER BY.

EDIT : If you are using SQL Server 2005, then have a look into therow_number()function

e.g.

CREATE STOREDPROCEDURE getProductRank
(
@.categoryIDint,
@.productIDint
)AS
CREATE TABLE #t
(
IDint IDENTITY(1,1),
ProductIDint,
CategoryID,
ItemsSold
)
INSERT INTO #t
(SELECT *FROM dbo.Products
WHERE CatehoryID = @.categoryID
ORDER BY ItemsSold)
DECLARE @.rankint
SELECT @.rank = IDFROM #t
WHERE ProductID = @.productID
RETURN @.rank
GO

|||

Hi Jagdipa,

I am using SQL Express 2005 and your reference was very helpful, however it still didn't remove the problem that I didn't want to retrieve all the records in the Category, as I only require 1 of the Products from the table (apologies for being unclear on this in the first post, I think you noticed as you declared a parameter - ProductID = @.ProductID).

I would like to call an aggregate function, something similar to the SUM() function as I mentioned previously.

I'm a little new to SQL so only the basic statements, and don't even know if its possible to achieve what I'm looking for without creating a temporary view or table as you've done?

Thanks.

|||

For those looking for the answer to this question, I have modified this from a custom paging example (which there appears to be lots of the web) to find the rank of an item in a table.

SELECT * FROM

(SELECT ROW_NUMBER() OVER (ORDER BY ItemsSold DESC) AS ProductRankNo, * FROM Products WHERE CategoryID = 10) AS AllProducts

WHERE ProductID = 23

To find the TOP 10 TOPSELLERS, simply change the "WHERE ProductID = 23" to "WHERE ProductRankNo BETWEEN 1 AND 10".

Bear in mind this will only work on SQL Server 2005 databases.

Initially, I was worried that the inner SELECT call would retrieve ALL the records in the table, but this doesn't appear to be so.

find a string within a string regardless of position within that string

I have a SQL query but need an extra column to output:
1234 if column D contains 'ABCD',
3456 if column D contains 'CDEF'
5678 if column D contains 'FGHI'
I have tried using CASE but I think that function needs an exact match
Can anyone help?
Thanks
Kay"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1137450168.459219.295770@.f14g2000cwb.googlegroups.com...
>I have a SQL query but need an extra column to output:
> 1234 if column D contains 'ABCD',
> 3456 if column D contains 'CDEF'
> 5678 if column D contains 'FGHI'
> I have tried using CASE but I think that function needs an exact match
> Can anyone help?
> Thanks
> Kay
>
SELECT
CASE
WHEN d LIKE '%ABCD%' THEN 1234
WHEN d LIKE '%CDEF%' THEN 3456
WHEN d LIKE '%FGHI%' THEN 5678
END
FROM your_table ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks guys, my syntax was slightly muddled!
Appreciate the help
Rgds
Kay|||SELECT
D,
[output] = CASE
WHEN CHARINDEX('ABCD', D)>0 THEN '1234'
WHEN CHARINDEX('CDEF', D)>0 THEN '3456'
WHEN CHARINDEX('FGHI', D)>0 THEN '5678'
ELSE '--not found--'
END
FROM your_table_name;
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1137450168.459219.295770@.f14g2000cwb.googlegroups.com...
>I have a SQL query but need an extra column to output:
> 1234 if column D contains 'ABCD',
> 3456 if column D contains 'CDEF'
> 5678 if column D contains 'FGHI'
> I have tried using CASE but I think that function needs an exact match
> Can anyone help?
> Thanks
> Kay
>|||"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1137452120.646322.28340@.g49g2000cwa.googlegroups.com...
> Thanks guys, my syntax was slightly muddled!
> Appreciate the help
> Rgds
> Kay
>
Perhaps you weren't muddled. There are two possible versions of the CASE
syntax. Maybe you were just thinking of the simple case version. The
following article has examples:
http://www.databasejournal.com/feat...cle.php/3288921
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--