|
i want to know this is only way ?
is there any sql syntax ( like TOP ) that return specified no of rows ?
MHF
|
|
|
|
|
SQL Server 2005 having ROW_NUMBER() function.
|
|
|
|
|
in SQL Server 2000 is not possible.,
you may try as navaneeth suggesting.
Regards
KP
|
|
|
|
|
Gents,
I have a string "abcd1;abcd2;abcd3;abcd4"
in my database on an MS SQL there are stored "abcd3" in a column. In the reverse situation I would be able to do a select statement like this
Select * from MyTable where MyColumn LIKE '%abcd3%'
Is there any easy solution to it in the reverse way so i fx could do something like
Select * from MyTable where 'abcd1;abcd2;abcd3;abcd4' LIKE MyColumn
Tried this by the way, didnt return any result or error so guess there are some solution to it.
Cheers,
-- modified at 6:28 Tuesday 14th August, 2007
|
|
|
|
|
this not correct because '%adbcd3%' not equal to '%abcd3%'
Select * from MyTable where MyColumn LIKE '%adbcd3%'
if you need show resault you must write select statment
Select * from MyTable where MyColumn LIKE '%abcd3%'
|
|
|
|
|
Hi Rami,
This was an typo in this topic on the example for the normal way to use like statement.
Thanks for the notification of the spelling error.
/Ronni
|
|
|
|
|
hi ronii
you solve the problem
if you not solving problem i need more explane about your problem
with regards
Rami Abd alhalim
|
|
|
|
|
nope didnt solve the problem. please read the whole text.
I have string "abcd1;abcd2;abcd3;abcd4" for example posted from a website or an application
In my database i have a string "abcd1".
I now need to find out if any part of the string ("abcd1;abcd2;abcd3;abcd4") is present in the column who hold fx "abcd1". This have to be done in a single select statement.
If done reverse "Find abcd1 in database with abcd1;abcd2;abcd3;abcd4 then a standard Select * from TableName where ColumnName LIKE '%abcd1%'" but is there an fast and easy to do that in a simliar way? just reverse in finding part of a string in the database?
/Ronni
|
|
|
|
|
add this function after that you can split string to compare between your string
=======================
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION [dbo].[fn_Split]
(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
--Name2 Varchar(8000)
)
AS
BEGIN
/*Declare @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
*/
DECLARE @index int
SET @index = -1
Declare @Text2 varchar(8000)
SET @Text2=@text
Declare @index2 int
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index >1)
BEGIN
SET @index2=len(Right(@text, @index - 1))
SET @text2=SUBSTRING(@text,LEN(LEFT(@text, @index+1 )),CHARINDEX(@delimiter , @text)-1 )
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text , (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
=======================
after add this function
select * from tablename where 'abcd1'in select value from fn_Split('abcd1;abcd2;abcd3;abcd4',';')
Rami Abd alhalim
|
|
|
|
|
How about:
select * from MyTable
where ';abcd1;abcd2;abcd3;abcd4;' like '%;' + MyColumn + ';%' Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Great thanks Andy. Worked like a charm.
Cheers,
Ronni
|
|
|
|
|
I am inserting a row into database from asp.net. Below is the command i had given:
insert_cmd = string.Format("Insert into CreateTask(AssignedTo,AssignedDate,EstimatedEfforts,Priority) values ('ABCD',14/08/2007,30,'Medium')");
The date i had given in the command is 14/08/2007, but when I check the table data in SQL server the date is displayed as ---- 1/1/1900 12:00:00 AM
Please help me out...
Sailaja
|
|
|
|
|
this problem maybe cause format date
if you need sure that set in command this date
solve:
insert_cmd = string.Format("Insert into CreateTask(AssignedTo,AssignedDate,EstimatedEfforts,Priority) values ('ABCD',08/14/2007,30,'Medium')");
|
|
|
|
|
Said could you plz tell me how to do that as i dnt know.
i'm new to sql
Sailaja
|
|
|
|
|
Insert into CreateTask(AssignedTo,AssignedDate,EstimatedEfforts,Priority) values ('ABCD','2007-8-11',30,'Medium'
In VB u have format function. U can use this function to format the date.
i.e. YYYY-MM-DD format.
|
|
|
|
|
thx pande... the code worked correctly if I give the date in single quotes but am not getting the current system time in it..
its displayed as:
8/14/2007 12:00:00 AM
Sailaja
|
|
|
|
|
If you want to insert the current date and time, use the Getdate() function. It's a Sql Server function that returns the current date and time. For instance:
insert into myTable(CurrentDate) values (GetDate());
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
my problem has been solved.
thank u so much...
Sailaja
|
|
|
|
|
i have a table, i want to display the values in rows into columns in sql server 2005..
Ex:-
<br />
<br />
a 1 x ----> a b c<br />
b 2 y ----> 1 2 3<br />
c 3 z ----> x y z<br />
Can anyone help me on this??
Dream it to achieve it
|
|
|
|
|
Hi
I have about 2k records in a table which contains a numeric field. I would like to know if there is any way I can select a set of records where the sum of this numeric field is limited to a particular value.
The table in question is part of an SQL 7 database.
Thanks in advance
Chandra
|
|
|
|
|
Hi Chandra
Do you mean:
select * from Orders where cust_id in (
select cust_id from Orders
group by cust_id
having sum(order_total) > 500)
order by cust_id This would receive the orders for customers who have placed more than $500 worth of orders.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi Andy
Thanks for your answer... but what if my table contains all distinct cust_id?
Regards
Chandra
|
|
|
|
|
Hi Chandra
Sorry - I got a little confused because your original question stated "... where the sum of ...".
Does
select * from Orders where OrderValue > 500 do what you want?
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi Andy,
I'm afraid this will not help either... let me explain with some data:
Field1 Field2<br />
3 34<br />
4 58<br />
5 97<br />
6 234<br />
7 345<br />
8 540<br />
9 679<br />
10 768<br />
11 2312<br />
12 4324
Now, I want to be able to pick up a set of records where the sum of Field2 is less than 5000.
I hope I have explained my requirement clearly
Thanks
Chandra
Modified to include /code tags since the tabs were swallowed up.
|
|
|
|
|
Hi Chandra
Thats a bit clearer - looks like some-sort of stock allocation problem. The simplest query would be:
select * from MyTable
where Field1 in (
select a.Field1
from MyTable a
inner join MyTable b
on b.Field1 <= a.Field1
group by a.Field1
having sum(Field2) < 5000)
order by Field1 The key is the link between a.Field1 and b.Field1 . However, this checks rows sequentially. If you have a record with Field1=100 and Field2=1 then earlier rows take precedence.
If you want a more optimised allocation query then you would need to resort to a UDF.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|