Hi,
I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue
My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers.
Thanks
Hi all.
I have been banging my head against a brick wall with this for a couple of days now. I am wrighting a custom front end for one of our SQLServer DB's and need to run a select agains the DateTime Field. The field Format is [DD/MM/YYYY HH:MM:SS]. I am tring to get the select to pull all the records on a set date only (keeping the time available but not selecting against it) so that the brass can see at what time on a given day an action was performed. Problem is, the way the programing software works, I can only build the SQL using string information. I have tried CONVERT and CAST both ways (converting the date to varchar and converting the string to date and also converting them both at the same time). Everything that I have tried returns an empty data set when used with the WHERE LIKE criteria (oh yeah, I'm also using LIKE % wildecards to retrive all the times against a specific date) but does populate when no filter is applied.
This is where my SQL is at just now:
SELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDate
FROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserID
WHERE (((CONVERT(varchar(10), FileStatusLog.StatusDate,103) LIKE '%{variable}%'));
This also returns an emtpy data set when the LIKE is replaced with ='{exact date entry in format: DD/MM/YYYY}', but does return the full table when the WHERE is removed and also when the LIKE is set to just a bare '%' with no other filter values.
Because of the fact that the results are returned when LIKE '%' is used I am not even sure if it is the CONVERT that is causing the problem. I have even tried assigning the escape character manualy to '+' incase the / values of the date in the LIKE variable where escaping other characters. Needless to say this did not make life any better.
As you may have noticed already SQL is not exactly my strong suit, but no one else here even knows what it is, never mind what it's used for, so I got droped with the task. Also - The DB was created by a third party so I have no write permissions to the DB.
Any help with this would be greatly appreciated as it's gotten personal now
Thaks in advance for any assistance
MS SQL 2000. Does anyone know how to find all rows where an nvarchar column contains a specific unicode character? Is it possible without creating a user defined function? Here's the issue. I have a table Expression (ExpID, ExpText) with values like 'x < 100' and 'y ≤ 200'. where the second example contains Unicode character 8804 [that is, nchar(8804)]. Because it's unicode, I don't seem to be able to search for it with LIKE or PATINDEX. These fail:
SELECT * FROM Expression WHERE ExpText LIKE '%≤%' -- no recordsSELECT * FROM Expression WHERE PATINDEX('%≤%', ExpText) -- no records
However, SELECT PATINDEX('%≤%', 'y ≤ 200') will return 3.
Any suggestions? Thanks in advance.
i am trying to search an SQL database to retrieve all names from employee table who have a birthday today. this needs to automatically fill in the date parameter with the system date. this is what i have so far:
sub page_load(sender as object, e as eventargs)
dtmDate=DateTime.Now.ToString("M")
con = New SqlConnection("Server=Localhost;UID=******;PWD=*****;Database=Pubs")
cmd = New SqlCommand("Select fname, lname From Employee where dob='& dtmDate'", con)
con.open()
dtrBday = cmd.ExecuteReader()
rptBday.DataSource=dtrBday
rptBday.DataBind()
dtrBday.Close()
I know this isnt quite right. i get errors when it hits my repeater.
the error i am getting is :Syntax error converting string to smalldatetime data type.
if someone could give me a push in the right direction here it would be greatly appreciated.
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Hello..
can anyone help me with this query string?
String SQL = "INSERT Employee(Employee ID, UserName, JobRole, Department, Level, Email)(SELECT max(EmployeeID) + 1 FROM Employee) AS Employee ID, VALUES(EmployeeID, '" + newUserName + "', '" + newJobRole + "', '" + newDept + "', '" + newLevel + "', '" + newEmail + "')";
I am trying to insert values into a table, but i have an Employee ID field, which needs incrementing. How can i do this through my SQL query string? Is this possible? As it can't accept a NULL value.
Thanks, Sandy
I'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result:
ColA ColB
"6abc" "0006"
"457def" "0457"
"7232hij" "7232"
I have implemented a temporary solution using a CASE statement:
SELECT ColA, ColB =
CASE
WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4))
WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3))
WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2))
WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1))
ELSE ''
END
FROM MyTable
Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution?
I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with:
DECLARE @ColBstring char(4)
DECLARE @num int
SET @ColBstring = ''
SET num = 1;
-- Get the numeric prefix from ColumnA's string
WHILE(isnumeric(substring(colA, 1, num)) = 1)
@ColBstring = (substring(colA, 1, num)
num = num + 1
-- Prepend the ColumnB string with zeros
WHILE(LEN(@ColBstring) < 4)
@ColBstring = '0' + @ColBstring
Thanks for any help,
Mike
I need to search a nvarchar field based on the format of the text. This field holds values in two formats: 000 000 000 000 and 000000. I only want to search through the records that are in the 000 000 000 000 format. Can anyone give me direction on how to go about doing this or give me some key words to search for on Google? Fixing this problem is not an option. This is a county tax DB from a poor county with almost a million records in it.
Thanks for the help!
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies View RelatedHi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies View RelatedHi,
I have an old db with streets names and another one with employees. I have to use both tables to check if an employee adress is ok, but I have the following problem:
Employee adress : 47th street nº12 2nd door b
street in db 47th street
The question is how to say to sql that searchs for the employee street in the streets db cause the employee addres is bigger than any streets in db and I have no results with street like '%47th street nº12 2nd door b%' or something like that.
What can I do?
Thanks so much
I have a parameter that is a varchar(50)
and I want to know if there are any percent(%) signs in the string,
what function can I use for this?
I am looking for the T-SQL equivalent of InStr()
Thanks,
Hi everybodyI have this query which need to extract names where I use the wildcard character % before and after the string to indicate that the string should not contain these characters but it is not working right .. Is there any way I could do that..SELECT name, famnameFROM inftable WHERE NAME NOT IN ('%socius%', '%rector%', '%superior%')In other words my result should not include those names that contains socius, rector, superior, this characters could begin with or in a middle of the field...thanks
View 2 Replies View RelatedHi,
Was wondering how you do the equivalent of an Oracle "Instr" function in TSQL, which returns the position of a string pattern in a specified string? I've looked through all the functions and can't find the equivalent.
Thanks
Simon
Hi,
Is there a T-SQL equivalent of the Oracle "Instr" function, whereby I can retrieve the position of a one specified charactier string within another? I have looked through the list of string functions and can't find an equivalent, which suprises me!
Thanks
Simon
For example
I want to search all the people in a database that names mary
I now that the sintax is something that looks like this.
SELECT Id, name
FROM Socios
WHERE (name LIKE '%string%')
But how i input this in the vs2008 query builder for it creates an automatic searchbox toolstrip?
I tried this but it didn't work
FROM Socios
WHERE (name LIKE ?)
The procedure below is for a 3 field wildcard search window.
It works fine if you enter a value such as cheese in @product_name.
If you enter cheese in @product_name1 or @product_name2 it returns nothing.
The string builds OK with values inserted where they should be, obviously there is a problem in the looping back of variable.
I've been looking at this so long I'm sure that the obvious solution is staring me in the face, but I just can't see it.
Any help would be appreciated.
Alter PROCEDURE usp_RobSearchTest
(
@product_name nVarChar(100),
@product_name1 nVarChar(100),
@product_name2 nVarChar(100),
@country_id nchar (3),
@language_id nchar (2)
)
AS
DECLARE @Variable nVarChar (100)
DECLARE @SQLString NVARCHAR(1000)
DECLARE @ParmDefinition NVARCHAR(1000)
/* Build the SQL string once. */
SET @SQLString =
N'SELECT product_name,unit_price,item_id,refund_price
FROM PRODUCT_LISTING
WHERE product_name LIKE ''%' + @Product_name + '%''
ORDER BY product_name'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@product_name nVarChar (100)'
/* Execute the string with the first parameter value. */
SET @Variable = @product_name
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@product_name = @Variable
/* IT WORKS UP TO THIS POINT */
/* Execute the same string with the second parameter value. */
SET @Variable = @product_name1
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@product_name1 = @Variable
/* Execute the same string with the third parameter value. */
SET @Variable = @product_name2
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@product_name2 = @Variable
Hello
I have a Database which contains like 1000 Tables.
I am not the designer of that DB.So I need to in which table and which column
that string exists. IS there a DBWIDE String search possible?
Thanks and Regards
I need to search a string starting at the end to find the
last space in the string, so I can determine the position of the last word in
the string.
Any ideas on how to do this are greatly appreciated.
Kellie
I am not very familiar with MS SQL, and I need to know how to search a table for a string. I know the column that it is in, but I don't know which record. I am looking for a small piece of text in a much larger text field (we are talkinga maybe a 10k char field and I am only looking for about a 15 char string).
How do I do this with a MSSQL query?
Thanks a bunch
Nevermind, i found PATINDEX(). That worked great.
I have access to a database's web front end and a limited amount of access to the server.
I am trying to find out what column a certain string is in.
I can see some text on the front end via the web that a user enters and then submits. This information must go to the back end of the database but I dont know where it goes...
I am able to provide a unique Sequence number that it would be linked with and obviously I have the string that im looking for its column name but other then that I dont know what I can do.
I don't have write access to the database so I am unable to make procedures. I am also not really aware how the front end works or if I would be able to find the script that deals with the text after the user hits "save"...
The 'LIKE' function looks for words that start with whatever is in the like condition. Is there an sql function similar but will look and compare at any part of the search string.
For example I am using a webservice in dot net to populate a dropdown list using this sql
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText
In this table there is a compound called SILCAP310 and I would like the search function to pick up 310 if I put this into the @prefix parameter. (but I would still like the search to perform like the 'LIKE' does also.
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText or compound_name SearchPartString @prefixText
Thanks in advance
I have at table that I want to update using another table which contains other values than those in the text column.
table1
id: text:
1 1111, 2222, 3333
2 1234, 2222
3 0123
table2
id: str:
1111 aaaa
2222 bbbb
3333 cccc
1234 qqqq
0123 oooo
I want to replace the text in table1 with the new values so that it looks like below:
table1
id: text:
1 aaaa, bbbb, cccc
2 qqqq, bbbb
3 oooo
My first idea was to split the text column in table1 and insert every new text with its id in a temporary table and then concaternate a new string and update the column.
But how do I manage this?
Hi All,
Please help, it is urgent
I am having a colume as image datatype, in which candidates resume is stored in binary format.
I have to search for particular words for eg. "ASP.Net, 2 Years" into that column
I have converted that column as varchar and then searching above two words(after spliting them by comma) into that string using CHARINDEX
DECLARE @i int, @j int
SET @i = select MIN(ResumeId) from table_name
SET @j = select MAX(ResumeId) from table_name
WHILE (@i <= @j)
BEGIN
DECLARE @Resume varchar(max)
SET @Resume = CAST((select Resume from table_name where ResumeId=100) as varbinary(max))
--code to search the particular word from @Resume
END
It is working fine, but it is very slow, taking 2+ minutes to scan 15000 records
How can I make it faster or is there any other way to search?
Thanks in advance
Hi
Is there any function to seperate a sting (VARCHAR) into two based on a character in the string (e.g. '-').
In short is there "InStr" or "split" type function in T-SQL ? If not what is the alternative?
I have a column called "Range" (VarChar) in the table which has ranges like 1-14 or 15-49 etc.
I need to select records by compare a variable (numeric) against these ranges.
So for instace if the variable has 15 its in range 15-49.
I was thinking of spliting the range field into two so that comparison is possible.
Any suggestions?
thanks
I have three input fields that I want to pass the values into a stored procedure.
It is not requried to enter in all three text boxes. What would be the best way to go about ignoring the other two text boxes if only one text box has input when trying to build the select statement
I was just wondering if anyone could tell me how to do a search for a partial data match. Say one data field is 123, 234, 345, 456 and another is 111, 222, 333, 444 and another is 555, 666, 777, 888 and I want to search for the unique number 234 but not the whole number 123, 234, 345, 456 ... is there any way to do that or does every search have to be exactly like the data in the field?
Thanks for any help.
Dennis
Dear all
I am pretty new to SQL server 2005 and have the following issue:
I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format.
I am having trouble composing the stored procedure to do that.
Any help would be apreciated.
Sincerely
d
Hi,
Is it possible to search a field in a database, extract CERTAIN data, and insert THIS data into another field in another table?
Example:
Address
---------------------------------------------------
18 BerryWood Drive, Midland, Doncaster, Y09 2JF
I want to extract the postcode from this field into another field in another table....
Example 2:
Name
-----------
Ivor Smith
I wish to strip the last name from the field, and put this into a field in another table..
David
Ever needed to find a stored procedure with a specific string in it? You can pretty this up as a stored procedure and pass it a parm or cut and paste it into query analyzer.
select name from sysobjects
where id = (select id from syscomments
where text like '%like%')
Edit:
The above works only for a single hit. For multiple hits, this works
select name from sysobjects as A
join syscomments as B
on (text like '%cursor%')
where A.id = B.id
Live and learn,
Cat
Edited by - cat_jesus on 08/22/2001 10:09:49
Edited by - cat_jesus on 08/22/2001 10:10:29