Wildcard Doesn't Match Using LIKE '%' On A Varchar Field, Wierd!
Jul 20, 2005
Right this has to be a Micro$oft mess-up surely...?
I'm running SQL 2k standard with SP3. I have a table which I'm trying
to query using a LIKE operator on a varchar field as follows
....
WHERE dbo.tbl_pm_projects.SeniorManagerID LIKE '%'
....
In actual fact the % is passed in by the application when the user
selects "All managers" from the drop down list used to select the
Manager to filter by. If they select a manager's name from the list
it becomes LIKE 'ajames' or whatever.
BUT - the table currently contains 2972 records. If I take out the
WHERE clause the SELECT returns all records - fine - but if I put the
where clause in it returns only 1682!! I thought the % was meant to
match, and I quote the SQL server Books Online files here; "Any string
of zero or more characters." Anyone explain to me whats going on
here?
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
SELECT DSNew, DTTM, RQDT FROM dbo.Feb INNER JOIN DMSEFL ON ACTR = DSNew where cast(DSNew as varchar(20)) = cast(ACTR As varchar(20)) If I run the above query I get zero recs back.
If I substitute a Value then I get the desired results (ie. where DSNew = '93235500') or if I enter (ACTR = '93235500') or if I put (where DSNew = '93235500' AND ACTR = '93235500')
Can anyone suggest a reason why this is happening. I know the records exist on both tables I ran the query in Acess and got the desired resutls.
When examining the "conversation_handle" column value in the Queue (from: "select * from ProcessQueue"), I find the value of the conversation_handle appears to be different from the "conversation handle" ("SEND ON CONVERSATION @conversationHandle...") on which the message was sent.
I'm trying to gather all messages for a given Dialog conversation, but am not able to do this because the conversation_handle doesn't appear consistent between messages in the Dialog.
note: the conversation_handle of "BEGIN CONVERSATION TIMER (@conversationHandle)..." message does appear to be correct in the Queue.
Thoughts on why the conversation_handle listed in the Queue might be different than that of the "SEND ON CONVERSATION @conversationHandle..." command that sends the message?
-- Begin Dialog from Service1 to Service2 on Contract BEGIN DIALOG @conversationHandle FROM SERVICE ExecuteProcess TO SERVICE 'ExecuteProcess' ON CONTRACT Process;
--- --- FROM DEBUGGER: @conversationHandle has value of AC8DF4E0-9F38-DB11-96A2-000CF1D46448 ---
-- Set Message value DECLARE @requestDocument xml SET @requestDocument = N'<ProcessRequestMessage/>';
-- Start Converstation time BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = @queueSeconds;
-- Send Message SEND ON CONVERSATION @conversationHandle MESSAGE TYPE ProcessRequest (@requestDocument);
Is it possible to replace a string value that uses a wildcard? For example, I'm trying to update a field on a table and replace the field value where it starts with a specific two character pattern and remove it from the string. The field is a delimited string and I want to replace any occurrence of ;A0% or ;A0* with a null value. I don't want to replace the entire field with a null value.
Let's say my field value is this:
;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;A0106;
I want the result to be this:
;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;
I was trying several variations:
UPDATE myTable SET myField = REPLACE(myField,'XX%','')
I got this weird problem and I was wondering whether anyone has an idea of how to resolve this.
I have a working report on the RS server, ran the report and tried to print it straight from the page by clicking on the Print button.
When it printed, it gave me additional pages as though the margins were incorrect. I have additional space on the left of the report, which pushes it out towards the right (very slight but noticable) which ended up printing "blank pages" ( basically only the page header). Moreover it didn't print any of the page footers ( which has the page numbers).
The weird part is that, I tried to export it to PDF format, and when I take a look at the pdf version, it looked fine with no formatting errors. Even printing the pdf works great.
I was wondering whether there was some bug with the RS Server or do I need to do somethin with the config file ? please advise. thanks !
Hi all,I've set up a page with a gridview, and I'm trying to create a query based on three parameters and to display all records when the page loads.I have created the first two parameters no problem, but I'm having problems with the last one.The parameter is to be populated by a dropdownlist, where the selectedvalue is numeric. I have done a search and found that I can't use the '%' wildcard as it is for a string data type.I have read somewhere that to get around this I can use the CHAR() function to convert to a character, I have tried this without success.When creating the ControlParameter the datatype gets set to string, which I think is not working becuase the input has to be an integer for it to conver to char, am I right?This is the query, and it is the @Application parameter that I'm having this problem with. SELECT dbo.Issue.IssueID, dbo.Issue.ReportedBy, dbo.Issue.ShortDescription, dbo.Issue.DateReported, dbo.Issue.Status, dbo.Priority.Description AS Priority, dbo.Application.Application FROM dbo.Issue INNER JOIN dbo.Priority ON dbo.Issue.Priority = dbo.Priority.PriorityCode INNER JOIN dbo.Application ON dbo.Issue.Application = dbo.Application.ApplicationID WHERE (dbo.Issue.Status LIKE '%' + @Status) AND (dbo.Issue.AssignedTo = @AssignedTo) AND (CHAR(dbo.Application.ApplicationID) LIKE '%' + @Application) ORDER BY dbo.Priority.PriorityCodeHas anyone managed to do this another way?Thanks
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
I have a written a dll in 2.0 that calls a webservice. This webservice is used to authenticate users in Active Directory. I created a assembly to that calls this dll because of the diverse languages versions that will use it (from asp,vb6 on up) and all can get values from a stored procedure that calls that assembly. I works great. Until now, I have to add another function to my dll that calls the webservice and returns the Users Full name from Active directory for electronic signitures. Okay I added to the dll then tried to reconstruct my Assembly and stored procedures and recieved the following error. "CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, void" I want to keep all these Active directory call all in one place so I can be consistant in all the different applications. I was reading about UDF but that could get messy as I have a config file for the dll that allows the user to dynamically change the url for the webservice. Any suggestions/help will be greatly appreciated
hi..i'm new in sql progaming,i try to make make a query that in table field "match" return to "1"if no member record in another table and return to "0" if there is anyrecord member :extable member:member idA 12B 14Table Incoming.member note matchC bla..bla 1A bla..bla 0D bla..bla 1...... ....... .....can anyone help me please?D
I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.
How can I remove all speical characters and then convert that field to an int?
Also I tried the following query which did not work as well, same error about conversion.
I need some store procedure script , from two table i want to pickup matching field and insert into main table ,give some sample store procedure coding...
If 1st table as cheque no and 2nd table as cheque no - matching then data would be inserted in to main table using two table...
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data. There is a web app already created that has the followig code to get the data from the database Dim strSQL ast string = "UsersSelectCommand" intLoginID = objDataReader("LoginID")
My stored procedure is the following: CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName GO The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer. For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD". Thanks.
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer. For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD". Thanks.
Hi guys I need immediate help with a query that I am trying to write. I want to sum the values in a query but the field has a type of varchar and it has decimal numbers too. So if I do the query something like that, that converts the field to int, I get the error message. I tried converting it into real or float but I get error message on that too. I need help with adding the calculatedValues and getting there sum. I would appreciate any help with that.
Thanks -Sarah Select SUM(Convert(int, calculatedValue)) from monitor.dbo.monHistory where LocalTimeWithoutDst > '8/26/06' and LocalTimeWithoutDst < '8/28/06'
This is the error message I recieve: Conversion failed when converting the varchar value '274.2' to data type int.
After reading Dan Guzman's blog entry (http://weblogs.sqlteam.com/dang/archive/2008/02/21/Dont-Bloat-Proc-Cache-with-Parameters.aspx) I started modifying some of my code to try it out and ran into a stumbling block. What size would you specific for a varchar(MAX) field? Since a varchar max field can hold up to 2 billion chracters I really don't think I need to specify 2 billion as the size. Anyone have any ideas?
I have a varchar column which containd comma delimited values like Rec# Fruits 1 Apple, Peach, Strawberry 2 Orange, Mango 3 Banana, Grape ...........
Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.
Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??
I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?
I would like to append information to a varchar field with an update statement, for example the field currently contains a name (Mark) and I would like to add information to name for business purposes, to update it to Markqw215, is this possible to do with an update statement? Thank you.
I would like to append information to a varchar field with an update statement, for example the field currently contains a name (Mark) and I would like to add information to name for business purposes, to update it to Markqw215, is this possible to do with an update statement? Thank you.
I'm importing data from a text file into SQL as a varchar, and I'm leaving it a varchar in its final destination table. It is essentially a price, i.e., $25.65. I'm using this price field (varchar) to perform a calculation...
Everything seems to work OK, but I'm not sure about using this varchar field to perform this calculation. Is this doable, or should it absolutely be converted to say, decimal?
I am setting up a database that will receive a lot of data from twoseparate telephone centers, the log table will in a short time haveover 1 million lines, and I was wondering if I should use 1 identifyfield or two:case 1:[Id] [int] IDENTITY (1, 1) NOT NULL[ServerId] [int] NOT NULLcase 2:[Id] [varchar(20)] IDENTITY NOT NULLWhere in case 1 I would just use a combination of Id and ServerId toidentify the line, where in case 2 I would have the Id field a varcharthat would look something like A-000001, A-000002 for server 1 andB-000001, B-000002 for server 2Which solution will be faster when searching for a record when thewill have over 1 million lines?
I need to insert data in a varchar column to that when its displayed new lines will appear in the text, that is the field will hold text with multiple lines...
Hi,I am using MSDE together with Enterprise Manager.I have a table with a field named description.This field will be filled by a web forms's textbox web control.The textbox's maxsize attribute is set to "3000" characters.What size do I have to adjust for my DB field description?Is the size of 3000 in Enterpise Manager equal to 3000 characters for the textbox?I am just trying to avoid errors if MSDE cuts off the string that comes from the textbox webcontrol.
