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%','')
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
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 tryingto 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 userselects "All managers" from the drop down list used to select theManager to filter by. If they select a manager's name from the listit becomes LIKE 'ajames' or whatever.BUT - the table currently contains 2972 records. If I take out theWHERE clause the SELECT returns all records - fine - but if I put thewhere clause in it returns only 1682!! I thought the % was meant tomatch, and I quote the SQL server Books Online files here; "Any stringof zero or more characters." Anyone explain to me whats going onhere?TIANiall
I need to run a replace on a varbinary field but I am unsure how. I have tried mulitple different methods and to no avail. I am stumped.
I have attached a zip folder that contains a spreadsheet that shows what I am trying to do. In the first tab it shows the SQL command I use to call the field I want to change. In this case it's the Content field and I am calling it using the content_key. As you can tell the content is stored as varbinary. In the second sheet, It shows this varbinary being converted to varchar(max) and it shows what the actual text says.
The table that this information is stored is is called digitalassetcontent
That is in front of the actual product description.
Somehow I need to run this command:
Code: Update digitalassetcontent set content = replace(CAST(content as varchar(max)), '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>','')
Where CAST(content as varchar(max)) like '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>%' AND content_key = 'desc214974236480438500781058983745755010'
This is the error message I get when running this code: Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.
Here is the example data <qMultipleChoice><qText>The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off 20on.</qText><qChoice>Strongly20Disagree</qChoice><qChoice>Disagree </qChoice><qChoice>Agree</qChoice><qChoice>Strongly%20Agree</qCh
I want result look like this First Column:The AE Understands what conditions the Account Manager is allowed to sign-off. Second Column: Strongly Disagree Disagree Agree Strongly Agree
This is what i had so far Select (SUBSTRING(QuestionText, (PATINDEX(N'%<qText>%', QuestionText) + 7),(PATINDEX(N'%</qText>%', QuestionText) - (PATINDEX(N'%<qText>%', QuestionText) + 7)))) From tblQuestion
my result: The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off%20on.
I have problem with replace '%20' and how to make the second column. Any Help? Thanks Shan
I am hoping someone will be able to help me with this. i have a table of accountholders with the following fields account_no account_name image_path the image_path field has a defaultvalue images/***.jpg I am looking for a query code to update the value in the image_path field by replacing the *** value with the value of the
I need to strip some puntcuation from any field in a given table.I'd rather like to avoid using the replace () for each field in thetable.Anyone have a nifty way do this?Is there a special name that I can use in the replace that means theentire row?(other than syntax, something like REPLACE(@ROW,CHAR(39),'') )tiaRob
Hi, I need to replace a character from a column of a txt file. I have defined the column and the values (datetime type for sql server) that I receive are like this: 2008-02-15-20.07.19 So, I need to replace the "." with a ":" beacuse those are minutes and sql server uses ":" for minutes How can I do this? Any help? Thanks
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
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
when insert a multiple line string value into a field it display with two squares when open table. insert into temptest (test,refdetails,cardno)values('test1','this is first line and second line and third line', '23243') this record shows in the database with squares for enter key. how to replace these squares with space? pat this is first line and [][]second line [][]and third line
Hi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
How to replace DateTime field with null value in SQL 2005 server I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value create aa @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate
I am trying to replace names found in 'xml' field name as hyperlinks, by matching with the names in database_tags.I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN
[code]...
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.Is there a way to get the function to recognize more than one names as hyperlinks.
Have a nvarchar column in ms sql that, based on user input, may or may not have a carriage return and a line break together (CHR(13)&CHR(10)), just a carriage return (CHR(13)), just a line break (CHR(10)). In using that column in ssrs, I need to find if any of the above exist and replace them with the string "x0Dx0A", "x0D", or "x0A" respectively.
System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Info.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Replacement' of 'Public Function Replace(Expression As String, Find As String,
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATEtbl SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000) FROM Table tbl JOIN Person p ON p.PersonID = tbl.PersonID JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2 WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%
'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.
Hello everyone and thanks for your help in advance. I am working on an application that does a property search off of a database that contains approximately 40000 records. The search criteria allows the use to specify a minimum and maximum price, subdivision name, number of bedrooms, etc. I set up a stored procedure to query the databse. if one of the parameters is not specified, i simply pass it a "%". However, when i execute this sproc in Query Analyzer, it takes in excess of 10 seconds to return the records, even if only one or two are returned. I am assuming this is due to the use of the wildcard character when the user does not have a preference, but I am not sure of any other way to do this. Any help on this topic would be grealy appreciated.
hi,i have an sqldatasource a gridview and dropdownlistthe gridview is updated on the selectedIndexChanged Event of the dropdownlistmy goal is to add an item in the dropdownlist with the name ALLwhich should matches all the records in the databasei tried to put the value of the all item = * and % but neither seems to workany help on what could be going wrong would be appreciated
Is it a good idea to have multiple contains? I have this query:Select * from myTable where contains (Col1, 'Africa') or (Col2, 'Africa')Also, I tried this, didn't return anything:Select * from myTable where contains (Col1, 'Africa*') or (Col2, 'Africa')Both Col1 and Col2 has the string 'Africa' and 'African' in it.--sharif
I am trying to control how users view records. I want to create a solution that would, for instance permit:user A to view Store 1user B to view store 2 and store 3user C to view store 5 and store 6and User D to view all stores even if more stores were added in the future (in other words user D would have access to all records) I want to create an 'authorization table' so that different users can see different records. I think the easiest way to do this is to pass a parameter to the where clause, but the problem that I face is the how can I use a wildcard in a SQL 'IN' clause? Does anyone have any suggestions. Perhaps I am taking the wrong approach. I would appreciate any guidance.
Hello, I'm trying to pull some data from a table with the option to filter on 2 columns. I've set up my sql statement to accept 2 parameters and I'd like to be able to send 1 or 2 wildcards if needed. My statement looks like this:SELECT * FROM City WHERE CityName= @CityName AND State= @State ORDER BY CityName For example, if you wanted all of the cities in all of the states I would pass (*,*) as parameters. Or if I wanted to see all of the states that have a city named Richmond, I'd pass (Richmond,*) as the parameters. The wildcards are not returning anything and I don't know why. It works fine if I pass something like (Indianapolis,Indiana) as parameters so I think it's in my use of the wildcards that is wrong. Thanks.
I'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.
Here's what I've got: ... and b.user_ID = CASE @user_ID WHEN 0 THEN '%' ELSE @user_ID ... ...the problem being the '%' part. That won't work on an integer column.
I've got a text field with a list of dates in this format:
10/31/2006 11/2/2006 11/4/2006 11/5/2006
The field can contain multiple dates (as listed above). I need to query the db and retrieve dates by month and year. For example, all fields containing 10/%/06 or 11/%/%06.
My query:
SELECT * FROM tblCalData WHERE visible='1' and approved='1' and eventDates LIKE '11%06%' ORDER BY eventDates DESC
so even though a field contains dates in October and November, selecting all November dates, in this example, don't appear. The query seems to be looking only at the first date.
Is this possible? Is there another/better way to do this?
everytime i want to query for any of tbl_c_? that contain a specific value i have to reference all 25 in my query. is there a better way? I cannot change the table.
HI Guys, I have a question. I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).
Current Statement: And tblRetailer_Contact.faxcontact LIKE *
I substituted: And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’
This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?
DECLARE @FaxContact as varchar (50)
SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5) SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', '')) SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5) SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', '')) SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)
SELECT tblEData.Timestamp As [TimeStamp], LTRIM(RTrim([ResultsCustName])) AS CustName, LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status], FaxContact AS FaxContact, ResultsPKey As ResultsKey INTO tmpE_Callbacks FROM tblEData LEFT JOIN tblContact ON tblEData.RetailerPrefix = tblContact.Prefix WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date AND FaxContact Like '%@FaxContact%'
I want to update data only where the value of the 'image_path' column is NOT = 192.168.150.12/images/*
Im basically trying to exclude creating duplicates, where this path already exists.
Here is my code:
INSERT INTO IMAGE (FCN, IMAGE_NAME2) SELECT FCN, Col066 FROM GRAB where Col066 <> ' ' update IMAGE Set PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg' FROM IMAGE WHERE image_name2 IS NOT NULL and perimage_path is NOT = 192.168.150.12/images/*
What is the proper code to do this. I know the last line does not work. Thanks
