SQL Query - Search Field For String
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
View Complete Forum Thread with Replies
Related Forum Messages:
Selective String Search Against DateTime Field
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
View Replies !
Search String Field (nvarchar) For Unicode Characters
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.
View Replies !
Using Date Field In Search String To Bind To Repeater
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.
View Replies !
Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.
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.
View Replies !
Increment An ID Field In Sql Query String
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
View Replies !
Parse Data From A String Field In A SQL Query
Ok,I have a field in a database that looks something like thisField1: BLAHBLAHBLAH (10.192.168.1)I need to either extract the IP address from this field or at leastformat it to only show the IP address in the result. The problem I amhaving with my limited experience is that the BLAHBLAH piece can varyin length and of course the IP piece can vary due to different IPaddress sizes. It seems to me there should be some kind of simplesolution since the IP address is enclosed but I cant "enclose" my mindaround what I need to do.
View Replies !
How To Manipulate String In Query And Create New Field
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
View Replies !
Query String Field Based On Text Format
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!
View Replies !
Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert
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
View Replies !
Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data
Hi - 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 Replies !
How To Search This String?
Hi, 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
View Replies !
Help With Search String
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 Replies !
Search A String
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,
View Replies !
Search String
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 ?)
View Replies !
String Search
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
View Replies !
String Search
Hi, 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
View Replies !
String Search And Conversions
Is there a way to search through a string and then convert them into something else? For example: Dallas, TX to TX Fargo North Datkota to ND D.C. to DC Twin Cities, M-N to MN I hope that clear. Troy
View Replies !
Search String In Reverse
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
View Replies !
Search A String Throughout The Database
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
View Replies !
Building Search String ?
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
View Replies !
Search For A String In Text
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.
View Replies !
Search Char In String
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
View Replies !
SQL Search String Function
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
View Replies !
Search String And Update Row.
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?
View Replies !
Report Wizard Doesn't Take A Simple Query In The Query String - Query Works In Query Builder When I Supply The Parameters
The following query in the query string: execute p_rpt_cli_v_index_reg_adj_exp_by_bkt2 @as_of_date='06/06/2007', @client_type=3, @index_as_of_date='05/31/2007' produces following error: There is an error in the query, invalid object name '#CLI_1', Invalid object name '#index'. When I open up the query Builder, and provide the same query and run, it asks for those 3 parameters values and after I provide those parameters, the query runs, but, clicking on Ok, produces same error as above in the 'Microsoft Report Designer' information window. The above query works in the Query Analyzer fine. What's so different in the Reporting Services env?
View Replies !
Search For A Specific Date When Specified As String?
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
View Replies !
Search String And Extract To Another Table
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
View Replies !
Optional Characters In Search String
I'm trying to search for commonly abbreviated company titles (ie limited, partnership, and so on). I would like to make my sql statement as short as possible (it's already quite lengthy as is). But I'm having trouble netting the abbreviated forms such as LTD and LMTD for limited (I have no control over the data I get, it comes from different counties with no standardization). I've tried using braketted strings like "L[I,IMI,M,]T[ED,D,]" and all other combinations I can think of, including using single quotes in the each string, and removing the empty placeholder and still can grab all instances. Someone else's insight would be appreciated.
View Replies !
Search And Update All Fields That Contains String
I hava at large database and I need to search all tables for the string 'NaN' and replace it to null to enable conversion. Is there any way to do this so that i do not need to write an update query for every column? Would appretiate an example if anyone knows how to manage this. Thanks!
View Replies !
Search For String In Stored Procedures
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
View Replies !
Search Field Issue
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
View Replies !
Partial Field Search
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
View Replies !
SQL Script To Search All Tables In A Database For A Particular String?
Is their a way to search every table in a database for a particular character string that might appear in any column in any of the tables? For example suppose in a hypothetical situation that the character string "http://www.DrSeusOnTheLoose.com" appears randomly in a database, but we do not know which tables it occurs in and in what columns. How can we do a search to find such a character string? As you know, this is easily accomplished in MS Windows XX or through a dos command line script. But when it comes to searching a whole database, I don't know how to do this. Can someone please help me? Ralph
View Replies !
How To Search For Fields Containing A Specific Text String
Hi All,I'm familiar with the syntax that looks like this:SELECT * FROM Users WHERE Email LIKE '%aol%'Which would return all users that whose Email column contains "aol".However, if I wanted to do that sort of a search using a prepared statement, how would I do it?I can't simply doSELECT * FROM Users WHERE Email LIKE '%@MatchAgainst%'Can somebody clue me in?
View Replies !
Wild Card Not Working In Search String
In the below section of code I'm connecing to a SQL database and when I use the "=" in the search it works ok, but when I try to add the ability to use wildcards by changin "=" to "LIKE" its not working Working code: Function GetName(ByVal first_Name As String) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name = @First_Name)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_first_Name.ParameterName = "@First_Name" dbParam_first_Name.Value = first_Name dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength dbCommand.Parameters.Add(dbParam_first_Name) Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet) Return dataSet End Function Code that I would like to work Function GetName(ByVal first_Name As String) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name LIKE '%' + @First_Name '+')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_first_Name.ParameterName = "@First_Name" dbParam_first_Name.Value = first_Name dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength dbCommand.Parameters.Add(dbParam_first_Name) Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet) Return dataSet End Function Any help would be greatly appreciated
View Replies !
Search For Specific Length String In Column
SQL 2000.I need a query that will search a field in the table that is 14characters long and begins with a number.I have a client that was allowing people to store credit card numbers,plain text, in an application. I need to rip through the table andreplace every instance of credit card numbers with "x" and the last 4digits. I got the replace bit going, but I am stuck on how to searchfor a string in a field of a specific length.Any ideas?Thanks,--Mike
View Replies !
Criteria; Search Any Word In Field
I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?
View Replies !
URGENT!!! Search Tool Function: Splitting A String
Hi All!!! I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem: The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned. So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character. Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID 2 FROM CRPL 3 WHERE (Trans LIKE '%' + @Trans + '%') OR 4 (Name LIKE '%' + @Name + '%') OR 5 (Address1 LIKE '%' + @Address1 + '%') OR 6 (Address1T LIKE '%' + @Address1T + '%') OR 7 (Address2 LIKE '%' + @Address2 + '%') OR 8 (Address2T LIKE '%' + @Address2T + '%') OR 9 (City LIKE '%' + @City + '%') OR 10 (CityT LIKE '%' + @CityT + '%') Thanks for all your help in advance!!!
View Replies !
Built In Function To Search The Occurence Of Char In String
Hi Everyone- i woder if someone know Built in function to search the occurence of char in string in T-SQL and iam not talking about the Index of the first occurence (e.g CHARINDEX ) but it is more generic i want to send to the function to ask about the index the second or the third or N occurence of the char. and i wonder if it is built in for performance reasons example getseachIndex("M|d|d","|",2)==> the result is 4
View Replies !
Why Search On Empty String Returns Results With A Space?
In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database. QA query based on the earlier script: Select * from StringTable WHERE (LongString = '' OR LongString IS NULL) My script: The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows: -- if LongString column is varchar - run varchar update else nvarchar update If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin -- update varchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL) END Else Begin -- update nvarchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL) END If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL) END Else Begin -- update nvarchar ShortString UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL) END My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue. Thanks
View Replies !
Search And Replace Only Replaces One Char Per Field
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script DECLARE @find varchar(8000), @replace varchar(8000), @patfind varchar(8000) SELECT @find = '"', @replace = '--THIS-WAS-QUOTES--' SELECT @patfind = '%' + @find + '%' UPDATE Incident SET description = STUFF(convert( varchar(8000), description ), PATINDEX( @patfind, description ), DATALENGTH( @find ), @replace ) WHERE description LIKE @patfind
View Replies !
Search And Replace Only Replaces One Char Per Field
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script DECLARE @find varchar(8000), @replace varchar(8000), @patfind varchar(8000) SELECT @find = '"', @replace = '--THIS-WAS-QUOTES--' SELECT @patfind = '%' + @find + '%' UPDATE Incident SET description = STUFF(convert( varchar(8000), description ), PATINDEX( @patfind, description ), DATALENGTH( @find ), @replace ) WHERE description LIKE @patfind
View Replies !
Script To Search For A String In All Varchar Columns In All Tables In A Database?
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.
View Replies !
Search For A Table/string Within Job Steps, Dts, Database, Stored Procedures Etc
Hi All :A couple of tables have been identified to be deleted. My job is tofind if it is at all used.On searching the web, i found a proc to search for a string within alldatabases in a server.using system sproc : sp_msforeachdbit searches for a string inviews, sprocs, functions, check constraints, defaults, foreign key,scalar function, inlined tablefunction, primary key, 'Replicationfilter stored procedure, System table, Table function, Trigger, 'Usertable, 'UNIQUE constraint''Extended stored procedure'So it is pretty extensive. But i dont think it is covering the codewithin execsqltasks in DTS, and tsql code within JOB STEPS. Those arethe two more places where code exists in my server.If any of you have done so in the past, do let me know if there is asystem stored proc or code that you have written, to do the samethanksRSLink to the above procedurehttp://www.sql-server-performance.c...ase_objects.asp
View Replies !
Full Text Search Doesn't Find A Specific String
Hi all, We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"') even though there exist records that satisfy the condition in the table, the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1" If I remove the last 1 from the search string i get lot of records, Can anybody help me out.
View Replies !
|
TRACKER
