is there a way to have a select statement which compares a value is like ('%a%','%b%','%c%','%d%','%f%','%l%')
so :
select address
from customers
where address like ('%a%','%b%','%c%','%d%','%f%','%l%')
???
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
I'm trying to run a SELECT statement to get two different values from a field that looks like this:
"Sample text [123], Sample text 2 [345]"
The two values I'm trying to grab has to be after the last comma. So in this case, I need to get
Value 1: 345
Value 2: Sample text 2
Is this possible to do? I can't create functions to accomplish this.
Hi,
I got a really simple question here. Say I have a table with
ID, Name
1, A1
2, A2
3, A3
....
10, A10
Now I want to combine the names into another table grouped by their ID (say 1-5, 6-10), so this new table has two names instead of 10:
A1 A2 A3 A4 A5
A6 A7 A8 A9 A10
Is there a function that allows me to 'combine' the names from a select statement?
Thanks in advance,
Steven
Hello, I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product. I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here: http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:
WHERE (OrderStatus IN ((select value from dbo.fn_Split(@StatusList,','))) OR @StatusList IS NULL) but now I don't know how to assign the string value to the parameter, say to '@solution_id'.my current select statement which was generated by Visual Studio 2005 looks like this: SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @solution_ID) ...but this only pulls results for the first item checked in the checkboxlist.Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I pass a parameter to it. But if I hard
code it, it gives me the right result.
The I check if the field value of 'Email' is exactly equal to the parameter '@Email'.
The field 'Email' is varchar, and CID is integer.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn
Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com'
return
Can anyone tell me the reason for it.
Thanking you in advance
Tomy
i have a case : i'm using a select statement with SqlCommand and save the results in the SQLDataAdapterand using the data table I post the result to the gridview and show it there....my question is what should I do to append string to the SQL Command??
View 2 Replies View RelatedI have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
When i use the following querry i get no result when i know i should be getting one.
Select *
from table1
where '16bbbT1' >= StartStr and '16bbbT1' <= EndStr
When i use
Select *
from table1
where '16bbbT1' >= StartStr
I get all the values above 16bbbT1
and conversly when i use
Select *
from table1
where '16bbbT1' <= EndStr
i get all the values less then 16bbbT1
I need to plug both where clause
can anyone help
need to look through hundreds of rows and compare two columns, and pull out rows where the date difference is 7 days
problem is one column stores the date as a string and values in that column look like this:
01/12/2007 10:15 pm
or
01/12/2007
the other column stores the date as seconds since 1/1/1970, which is good and easy to work with..
how do I write the select?
any ideas
thnx
tony
Hi I wanna put string in query "where" part.For example,$sql="select VEHICLEFROM databaseWHERE MECHANIC =BRIAN";like above, "mechanic" column is filled with strings. Then how can Iwrite "where" part?Above query does not work.Thanks.
View 2 Replies View RelatedOk, here is what i'm trying to do and its driving me nuts.
ok,
1) I have a proc that runs and needs to validate the user prior to running - this proc is called from an hand held device
2) the id and password are being passed as "clear text" but the password is stored in the database table hashed.
Is there anything on the db side that can get the hash value from the password column of the aspnet_membership table and compare it to the password being passed in to this proc? I have suggested several options to the handheld developer but nothing. This has to be done on the database side.
so,
username and password are passed to proc from handheld.
proc needs to validate ther user in the aspnet_membership table
if user id and password are valid execute the stored procedure
is this possible? if so can ANYONE point me to some examples of this being done?
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
I have used a query statement with the following WHERE string to 'Fill' a dataset.
"AND (A.ApptsDate > '" & strApptPreDate & "' OR (A.ApptsDate = '" & strApptPreDate & "' AND A.ApptsTime >= '" & strApptPreTime & "' ))" & _ and strApptPreTime is defined as:Dim strApptPreTime As String = SomaShared.strPadTime(CStr(dApptCalcNewDate.Hour) + ":" + CStr(dApptCalcNewDate.Minute))
Somehow, the dataset showed only the ApptsTime after 10 am. After more than 2 weeks of debugging, I still can see a dataset watch for > 10 amAppsTime only. Now I am guessting, the problem is 9 is different from 10 - 16, it all because 9 is single digit. Then I check the data type settings for these variables. Here are what I found:
In SQL Server Agent job, the ApptsTime data was 'inserted' by @NewApptTimes, which is declared as char(5).In SQL Server database, the ApptsTime was defined as nvarchar(15).
My question are: 1. The reason why there were no 9 am data for the dataset, is becasue 9 am of nvarchar(15) is not > 8:30 of strApptPreTime? 2. If the answer to the quation 1 is yes, how do I define AppsTime and/or strApptPreTime?
TIA,Jeffrey
Hi All,
I have a column in my table like so:
'D4B00 L2A00 L3A00 L6C00 P1C00 L2A28 P4B00 '
How do I check in SQL if any pieces have the first 3 character the same. In the above case, L2A is present twice. I need to do this because I need display disctinct items, therefore L2A needs to be displayed only once.
Any help is appreciated.
Thanks
I need to compare to string in sql
requirement is
Select Reson_id from reason_data where reason='ryete / werewr ddsad '
is there any way without string function? bcz string function take more exaction time
CREATE FUNCTION [dbo].[Compare]
(
@usrVal varchar(100),
@dbVal varchar(100)
)
RETURNS BIT
AS
BEGIN
DECLARE @flag BIT
DECLARE @userStrLen INT
DECLARE @dbStrlen INT
DECLARE @counter INT
--Default assignments
SET @counter=1
Set @userStrLen=len(@usrVal)
Set @dbStrlen=len(@dbVal)
IF (@userStrLen != @dbStrlen)
BEGIN
return 0
END
ELSE
BEGIN
WHILE @counter <= @userStrLen
BEGIN
IF (substring(@usrVal,@counter,1) != substring(@dbVal,@counter,1))
RETURN 0
SET @counter = @counter + 1
END
return 1
END
RETURN @flag
--SELECT dbo.Compare('sqlserver user defined functions','sqlserver is the best backend')
END
Hi,I have a funny Error in our sql server 7 & 2000. When I compare twostring in Unicode format. I receive that it is the same when I add newnchar.I don’t understand why?Could you help me ?declare @str nvarchar(128), @str2 nvarchar(128)Set @str =nchar(21121)+nchar(49352)+nchar(47811)+nchar(48256 )+nchar(4966)+nchar(25736)+nchar(1788)+nchar(51220)+nchar(17733)Set @str2 =nchar(21121)+nchar(49352)+nchar(47811)+nchar(48256 )+nchar(4966)+nchar(25736)+nchar(1788)+nchar(51220)+nchar(17733)+nchar(4 1273)select @strselect @str2SELECT DIFFERENCE(@str, @str2)if soundex(@str) = soundex(@str2)BEGINSELECT 'OK'ENDELSEBEGINSELECT 'KO'ENDif @str = @str2BEGINSELECT 'OK'ENDELSEBEGINSELECT 'KO'END-=zoltux=-*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHello,
I have a table called INVENTORY.
The fields in the table are:
inventory_id
warehouse_id
item_id
slot
qty
on_hand_qty
The support tables are the item table and the warehouse table
I am trying to create a report showing the inventory of items of warehouse_id 1 with warehouse_id 2. If there is no inventory for either warehouse then suppress it. That in itself isn't that hard to do, but I would like to put them side by side, and that I'm having a hard time with.
I hope i didn't confuse the question, and any help would be greatly appreciated!
I have created a function with:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
how can I pass the variable @item correctly for the string comparison
WHERE item = @item AND week = @week
to work correctly please?
WHERE item = '@item' AND week = @week
won't work and
WHERE item = @item AND week = @week
won't work.
Hi Guys,
I have a table "HOLIDAY" with column "HOLIDAYSTART" and "HOLIDAYEND" in date/time type.User can apply new holiday on a form with value "STARTDATE" and "ENDDATE", but how do I check if the new holiday dates already exist in the table??
I looked around and found you can use something similar to following to check for single date,select * from holiday where ('1-12-2007' >= HOLIDAYSTART) AND ('1-12-2007' <= HOLIDAYEND)
but how would I do it with my situation above with between 2 dates? any help? Thanks in advance.
We're converting to new student info system. Sometimes registrar entered the same school into the schools table but spelled it differently. Trying to find all student assigned transfer credits from the same school but the school name is different. My db shows a max of 9 different schools students have rec'd transfer credits. Spending too much time trying to figure out best way to do it w/o a ton of IF stmts. Looking at Soundex and Difference functions. Still looks like a lot of coding. how to compare up to 9 string variables in sqlserver 2008?
View 2 Replies View RelatedHello,In my table, I have two columns - ForecastSales and ActualSales. I needto write a query that returns me just one aggregate value (one row andone column). If sum(ActualSales - ForecastSales) is negative, I need toreturn "red." Otherwise, I need to return green.I looked at CASE statement. However, I could not figure out anefficient way to build this query. I would appreciate your help.Thank you in advance for your help.Pradeep
View 3 Replies View RelatedI compare two table with JOIN statement. Now I would like to update one of them base on result. How it to do?
View 2 Replies View RelatedI try to compare and select data from table that which not in other table
Select
AAA_Id
,BBB
From dbo.Table1 a
Where AAA_Id not in(Select b.AAA_Id
From dbo.Table2 b, dbo.Table1 a
Where b.AAA_Id = 11)
But when I modified it like:
Select
AAA_Id
,BBB
From dbo.Table1 a
union
Select
0
,'Select'
From dbo.Table1 a
Where AAA_Id not in(Select b.AAA_Id
From dbo.Table2 b, dbo.Table1 a
Where b.AAA_Id = 11)
Order by 2
I cannot to get same result.
How to select a column to compare two values in it with AND
Select distinct c.reg#, a.fname, a.lname, to_char(b.L#) as "L"
from employee a, driver b, trip c
where a.e#=b.e#
and b.l#=c.l#
and (c.reg#='PKR3344'AND c.reg#='WWF1155')
order by c.reg#;
error no rows selected..I want c.reg# for the two values inclusive
Hi All ,
I am having a table which contain data in below form
id startvalue endvalue
1 12:00a 12:29a
2 12:30a 12:59a
3 1:00a 1:29a
4 1:00p 1:29p
5 2:00a 2:29a
Through SQL query I want to select the id column value for the row returned for the following query
select id from mytable where '1:24a' >= startvalue and '1:24a' <= endvalue
Idea is I will pass a literal value that lies between the startvalue (nvarchar column) and endvalue (nvarchar column) to fetch the id of the row returned, which should be 3 But when I run the query it returns me 2 rows for id 3 and 4.
I believe this is because I am trying to compare nvarchar columns with ">=" and "<=" operators.
Can anyone suggest how i can select the correct value or how to do this.
Thanks,
Ashish
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedOk I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies View RelatedHi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
 UPDATE TABLE1
  SET TABLE1.FIELD2 = 1
 ELSE
  UPDATE TABLE2
  SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.