|
i have just tested it, but the problem is, i have so many entries in that column. when i write a column name, it does not replace the commas, instead it returns the column name not even the entries in that column. eg.
like i said, the values in that column are of this shape:
01,02,M,045,1948,2,21 and the column name (field) is say column1 i will write:
select replace('column1',',','') it only returns column1 not 0102M0451948221.
please help me on this. 
phokojoe
|
|
|
|
|
phokojoe wrote: select replace('column1',',','')
is not correct bcause you have write columnename inside single qutes
Try again code down below
<br />
Select replace(columname,',','') from tablename
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
yah yah yah, without you i could have not survived this stress.
i have done it! it works.
thanks once more.
phokojoe
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi friends, I would like to query with a string variable:
For example if using
select * from Members
I get
ID(bigint) Name(varchar(max)) Owner(varchar(max))
1 N1 a000001
2 N2 a000002
3 N3 a000003
4 N4 a000003,a000004
5 N5 a000001,a000002
Then if using
select * from Members where Owner like '%a000001%'
I would get
ID(bigint) Name(varchar(max)) Owner(varchar(max))
1 N1 a000001
5 N5 a000001,a000002
Then I use a string variable test to query as follows:
System.String test="a000001";
select * from Members where Owner like '%+test+%'
I get nothing.
So could you please help me that:
how can I use the test to query with the same result as
select * from Members where Owner like '%a000001%'
when test="a000001"?
Thanks for any feedback.
|
|
|
|
|
chenayang wrote: select * from Members where Owner like '%+test+%'
This query looks for 'test' not 'a000001'. Since 'test' is a variable, you should append it.
System.String test="a000001";
string query = string.Concat("select * from Members where Owner like '%", test, "%'");Note, this method is open to SQL injection. Use parameterized queries always.
|
|
|
|
|
Thanks a lot, N a v a n e e t h
I solve the problem as follows:
System.String test="a000001";
string query = String.Concat("select * from Members where Owner like '%", test, "%'");
|
|
|
|
|
I'm running the following query and it is giving me a major problem.
SELECT Listings.ListingID, Listings.Title, Listings.HasPhoto, Listings.IsTaken, Listings.IsPickedUp, Listings.TakenDate, Listings.PickedUp, Listings.TimesViewed, Categories.CategoryName, Members.dbo.Members.LoginID, Listings.Submitted, Listings.Approved, GetTakenMemberName.LoginID AS TakenMemberName
FROM Listings INNER JOIN
Categories ON Listings.CategoryID = Categories.CategoryID INNER JOIN
Members.dbo.Members ON Listings.MemberID = Members.dbo.Members.MemberID INNER JOIN
Members.dbo.Members AS GetTakenMemberName ON Listings.TakenBy = GetTakenMemberName.MemberID
WHERE (Listings.MemberID = @MemberID)
If I remove
INNER JOIN Members.dbo.Members AS GetTakenMemberName ON Listings.TakenBy = GetTakenMemberName.MemberID
which is the last join and
GetTakenMemberName.LoginID AS TakenMemberName
from the SELECT portion, the query runs without a problem and gives a result. With those statements in however the result is an empty table. Currently there is only one record in the Listings and Members table, so as far as I'm concerned TakenMemberName should be returning as null or empty.
I even tried using an all 0 guid in the TakenBy field so at least the query would have something to look for during the SELECT. However, that didn't work either.
[Modified]
On further investigation I have found that if I put an actual member in the TakenBy field that the query executes with out problem, but if the query doesn't match a member then the entire select fails. How can I prevent that from happening?
modified on Wednesday, August 27, 2008 10:08 PM
|
|
|
|
|
Sounds like you need an LEFT OUTER JOIN instead.
|
|
|
|
|
Thank you very much. Changed the join and it worked perfectly.
I have so much to learn about SQL it unbelievable to me somedays. Thanks again for the help. Been stuck on this for about 4 hours.
|
|
|
|
|
I created a Stored Procedure to create random strings of the length and complexity desired. It is based on the widely published spt_values query for near random numbers.
It is functional, but I'm not entirely happy with the script. There has to be more elegant way to handle different complexity settings, other than the IF...ELSE clauses I used.
CODE:
<br />
-- Random String Generator<br />
-- By: Jesse Wimberley<br />
-- 27 August 2008<br />
-- Description: Creates Random strings of <br />
-- variable length and complexity.<br />
CREATE PROCEDURE [dbo].[up_RanGen]<br />
@Return varchar(200) = '' OUTPUT,<br />
-- @Len for the length of the output<br />
@Len int = 8,<br />
--Complexity set with @Complex<br />
-- 1 = Uppercase Letters<br />
-- 2 = Uppercase and Lowercase Letters<br />
-- 3 = Uppercase, Lowercase and Numbers<br />
-- 4 = Complex Password<br />
@Complex int = 4<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
declare @Random varchar(200)<br />
-- @a thru @f set the ranges of acceptable values for each character<br />
declare @a int, @b int, @c int, @d int, @e int, @f int<br />
If @Complex = 1<br />
BEGIN<br />
set @a = 65<br />
set @b = 80<br />
set @c = 81<br />
set @d = 82<br />
set @e = 83<br />
set @f = 90<br />
END<br />
ELSE<br />
If @Complex = 2<br />
BEGIN<br />
set @a = 65<br />
set @b = 90<br />
set @c = 97<br />
set @d = 100<br />
set @e = 101<br />
set @f = 122<br />
END<br />
ELSE<br />
If @Complex = 3<br />
BEGIN<br />
set @a = 65<br />
set @b = 90<br />
set @c = 97<br />
set @d = 122<br />
set @e = 48<br />
set @f = 57<br />
END<br />
ELSE<br />
BEGIN<br />
set @a = 48<br />
set @b = 60<br />
set @c = 61<br />
set @d = 75<br />
set @e = 76<br />
set @f = 122<br />
END<br />
set @Random=''<br />
select @Random=@Random+char(n) from<br />
(<br />
select top (@Len) number as n from master.dbo.spt_values <br />
where (type='p' and number between (@a) and (@b)) or (type='p' and number between (@c) and (@d)) or (type='p' and number between (@e) and (@f))<br />
order by newid()<br />
) as t<br />
set @Return = @Random<br />
END<br />
The four complexity settings are based on ranges of values returned as their ascii equivalents. Upper/Lower/Numbers has three ranges of numbers. For simplicity, I stretched all four settings across three ranges, even when only one or two ranges were needed.
|
|
|
|
|
Hi,
how can i get info on the 2nd max(id) or 3rd min(money) record on a table.
I only know how to get min(X) or max(X) record on a table
Thanks 
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Select Max(id)
From <code>table</code>
where id != (Select Max(id) From <code>table</code>)
The above will give you the second maximum id.
|
|
|
|
|
Another way:
<br />
select top 1 ColumName from TableName<br />
where ColumName < (select max(ColumName ) from TableName)<br />
order by TableName desc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
To select the 5th lowest (or n-th lowest) replace the "5" in this query with the number you want.
Select max(X) from
(Select top 5 id as X from idTable order by id asc)t
To select the n-th highest replace the "5" in this query with the number you want.
Select min(X) from
(Select top 5 id as X from idTable order by id desc) t
Cheers!
|
|
|
|
|
Hi,
i need some intel on arrays and loops. I only know how to use the while loop. I sometimes write store_procedures that have more 100lines.
Some tutorial would be a good help to me (on how to perform loops or array)
declare @id_user1 as int
declare @id_user2 as int
declare @id_user3 as int
...
declare @id_user10 as int
i can´t do this
while @a < 10 + 1
begin
set @id_user[@a] = 'whatever'
end
thanks 
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Thats because you can't do sql like that, there is no concept of an array. To do it like that you need to create and execute the sql dynamically
declare @id_user1 as int
declare @id_user2 as int
declare @id_user3 as int
declare @sql nvarchar(2000)
declare @a int
set @a =1
while @a < 11
begin
select @sql = 'set @id_user' + convert(varchar,@a) + ' = 1'
exec (@sql)
select @a = @a +1
end
try Google for dynamic sql, there are lots of examples.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
The best way to simulate arrays in SQL is to use the table variable (which I see you posted about just before this post)
So to do the above, you would have something like this:
DECLARE @table TABLE
( id INT, id_user varchar(200))
declare @i INT
Set @i = 1
While @i <= 10
BEGIN
insert into @table(id, id_user)
values(@i,'whatever' + cast(@i as varchar))
Set @i = @i + 1
END
-- Test it:
Select * from @table
Hope this answers your previous question as well!
Cheers!
|
|
|
|
|
Hi,
how can something like this?
declare @my_table as table (id int,f_name varchar(50),l_name varchar(50))
insert into @my_table values (select id_user,first_name,last_name from dbo.users)
nelsonpaixao@yahoo.com.br
|
|
|
|
|
So whats the question?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I think you are looking for something like
declare @my_table as table (id int,f_name varchar(50),l_name varchar(50))
insert into @my_table (id, f_name, l_name)
select id_user, first_name, last_name
From dbo.users
|
|
|
|
|
I have a table called CostAverage which stores the average value of an item and the date that it was calculated on. Each item has its cost average calculated on different days, and only when changes are made to the item. Since the OnDate could be anything, and the table retains the history, I am looking for a way to get the most recent cost average for each item in the table. Here is a snapshot of the table:
CostAverage
PK CostAverageID
FK UserItemID
CostAverage
OnDate
At first glance, I threw down something like this:
SELECT UserItemID, CostAverage, MAX(OnDate)
FROM CostAverage
GROUP BY UserItemID, CostAverage
Since the CostAverage is always different, that of course returns nearly the entire table. I can't really use an aggregate on the CostAverage since I need the most recent.
Does anyone have a suggestion? I am sure that I wrote a similar query once in the past, but I can't seem to find it (or remember it)
|
|
|
|
|
Try this
SELECT UserItemID, CostAverage,OnDate
FROM CostAverage ca1
where OnDate = (SELECT MAX(OnDate)
FROM CostAverage ca2 where ca2.UserItemID = ca1.UserItemID)
Personally, I would add a flag to indicate the current record and maintain it via a trigger as the above may be slow depending on your indexing.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank you, that works quite well. The query runs around ~2 seconds, this table should be weeded out in the next 6 months during a refactor so I'm not too worried about performance yet.
Again, thanks.
|
|
|
|
|
No problem
Bob
Ashfield Consultants Ltd
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
