Database

14-Aug-07 0:11

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

Rami Said Abd Alhalim14-Aug-07 0:24

14-Aug-07 0:24

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%'

Ronni Marker14-Aug-07 0:28

14-Aug-07 0:28

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

Rami Said Abd Alhalim14-Aug-07 0:32

14-Aug-07 0:32

hi ronii
you solve the problem
if you not solving problem i need more explane about your problem
with regards

Rami Abd alhalim

Ronni Marker14-Aug-07 0:47

14-Aug-07 0:47

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

Rami Said Abd Alhalim14-Aug-07 2:28

14-Aug-07 2:28

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

andyharman14-Aug-07 1:04

14-Aug-07 1:04

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".

Ronni Marker14-Aug-07 2:45

Problem in inserting date for datetime field

14-Aug-07 2:45

Great thanks Andy. Worked like a charm.

Cheers,
Ronni

potlakayala14-Aug-07 0:09

Re: Problem in inserting date for datetime field

14-Aug-07 0:09

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... Frown | :(

Sailaja

Rami Said Abd Alhalim14-Aug-07 0:29

Re: Problem in inserting date for datetime field

14-Aug-07 0:29

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')");

potlakayala14-Aug-07 0:37

14-Aug-07 0:37

Said could you plz tell me how to do that as i dnt know.
i'm new to sql Frown | :(

Sailaja

okdeshpande14-Aug-07 1:57

okdeshpande

14-Aug-07 1:57

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.

potlakayala14-Aug-07 2:28

14-Aug-07 2:28

thx pande... the code worked correctly if I give the date in single quotes but am not getting the current system time in it.. Frown | :(

its displayed as:
8/14/2007 12:00:00 AM

Sailaja

Pete O'Hanlon14-Aug-07 2:50

Pete O'Hanlon

14-Aug-07 2:50

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.

potlakayala14-Aug-07 3:14

How to put values in rows to columns in sql server 2005

14-Aug-07 3:14

my problem has been solved.
thank u so much... Rose | [Rose]

Sailaja

Bhaskar Ravindranath14-Aug-07 0:05

Bhaskar Ravindranath

14-Aug-07 0:05

i have a table, i want to display the values in rows into columns in sql server 2005..
Ex:-

 a 1 x ----> a b c b 2 y ----> 1 2 3 c 3 z ----> x y z 

Can anyone help me on this??

Dream it to achieve it

Summation in SQL Query

ChandraRam13-Aug-07 23:49

13-Aug-07 23:49

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

andyharman14-Aug-07 0:57

14-Aug-07 0:57

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".

ChandraRam14-Aug-07 1:27

14-Aug-07 1:27

Hi Andy

Thanks for your answer... but what if my table contains all distinct cust_id?

Regards
Chandra

andyharman14-Aug-07 1:35

14-Aug-07 1:35

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".

ChandraRam14-Aug-07 1:53

14-Aug-07 1:53

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 Smile | :)

Thanks
Chandra

Modified to include /code tags since the tabs were swallowed up.

andyharman14-Aug-07 2:57

14-Aug-07 2:57

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".

ChandraRam14-Aug-07 3:34