Find And Replace Syntax

May 10, 2001

Is there a way (using a combination of "stuff" and patindex")
to do the following:

replace this --> 1 2 3 4 5
with this --> 12345
or better yet --> 1,2,3,4,5

note: these "spaces" are tab delimited!

any help would be appreciated!
TB

View 1 Replies


ADVERTISEMENT

Replace Syntax Error

Apr 12, 2007

Hello
In my database table I have replaced  <h3> Some Text </h3>  with <h2> Some Text </h2> in the Description column as below: 
UPDATE CAT_Products
SET Description = replace (Description, '</h3>', '</h2>')WHERE Description <> '</h2>'
UPDATE CAT_ProductsSET Description = replace (Description, '<h3>', '<h2>')WHERE Description <> '<h2>'
However, when I try the same replace syntax code with the DescriptionHTML column in the same table, it does not work, e.g.
UPDATE CAT_ProductsSET DescriptionHTML = replace (DescriptionHTML, ' &lt;h3&gt; ', '&lt;h2&gt;')WHERE Description <> '&lt;h2&gt;'
What do I need to adjust?
Thanks
 
 
 
 

View 10 Replies View Related

Find And Replace

Sep 13, 2006

To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.

View 1 Replies View Related

Find And Replace.

Apr 30, 2004

Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time?

That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do.

Thank you to whomever can help.

Alec

View 1 Replies View Related

Find And Replace......EVERYTHING?!

Jan 4, 2007

Hi I run a find and replace in my script to replace some key words.

Little did I know that in SQL Server 2005 that it actually finds and replaces ALL query windows open for that database.

Any ideas on how I can turn this setting off? so that it only finds/replaces within the query window that I am using.

Thanks in Advance!

View 1 Replies View Related

Find Replace?

Jan 9, 2008

Hello,

Wondering if anyone may have any advice on how to do this?

I need to do Find/Replace of text in a varchar field so that it looks for text I want then it replaces that text with the new text and updates the field with that specific change, leaving everything else intact.

And I need to do this for every row in a table.

Thanks in advance!!

--PhB

View 3 Replies View Related

Find And Replace

Jan 23, 2008

Hello All,
I have a master file with

Slcode Sldesc
S0001 Furniture & Fittings
S0002 Investments
S0003 Coolie & Cartage

I want to find the ampersand character in between and replace with and.

Nirene

View 4 Replies View Related

Updating - Find And Replace

May 30, 2007

Basically I want to do a find and replace.
All the rows where column1 starts with F: I want to replace with M:
I tried this without luck

update table
set left(column1,1)='M'
where
left(column1,1)='F'

thanks

View 5 Replies View Related

Find And Replace Values

Dec 7, 2007

I was wondering how to do a find and replace with SQL? Would I use a SET statement?

I need to find a specific value and replace all of the results with a different value.

Thanks for all responses.

View 1 Replies View Related

Find And Replace Table Name

Feb 21, 2008



Hi

acutally due to certain reason i have changed the few tables name in my database, but now i m facing problem in my software, i have alot of procedures and function as well in my database and these are using old table name,
Is there any script that i can use to replace the name of the tables.
i have checked few procedure/function in which i use table name with different style like [dbo].[tablename] and in some places i just use only [tablename].

Can anyone help me ?


Thanks and looking forward.

View 4 Replies View Related

Find/Replace ColumnNames In All SPs/Views

Feb 25, 2001

I would like to change a column name, but before doing so would like to search all SPs and Views for occurrences of that name. I would be happy with just a list of them, but even cooler would be the ability to change all the occurrences to the new name.

Thanks!
Arthur

View 1 Replies View Related

Global Find And Replace Utility For SQL

Jun 11, 2004

Howdy all,

I am looking for a good global find and replace utility for SQL Svr. I have tried the Speed Ferret demo which seemed to work well but did not address code outside of the databases such as DTS. Does anybody know of a utility that can go beyond databases?

Thanks

View 3 Replies View Related

How To Find And Replace In Entire Database

Nov 21, 2013

I have to replace gl_number 25050-80 with gl_number 25050-80 in the entire database. I know how to do that in a table but not in all tables.

View 2 Replies View Related

Find And Replace String Manipulation Help

Mar 14, 2008

Please help me with the sql script to manipulate the string data:

I need to add <Text> at the beginning and end of the string.
I also need to add <option> before the first and after last occurence of the <Option> string. The original string

<StockNumber>502</StockNumber>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
<Warranty>1</Warranty>

should look like

<Text>
<StockNumber>502</StockNumber>
<Option>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
<Option>
<Warranty>1</Warranty>
<Text>


Thanks.

View 6 Replies View Related

Pattern Matching Or Find And Replace In SQL Query

Oct 29, 2007

I have a table called MessageBoard.  It has a column called Messages.
A user can type text including any html tags through a text area ans when he saves it by clicking a button, the content typed by the user is saved in the MessageBoard Table (in the Messages) column.  So once saved, the html tags are kept intact.  If I have to find and replace certain html tags, what kind of SQL Query I have to write?
For example I want to find all the <pre> </pre> tags and replace it with <p> </p> tags.  How do I do this?

View 6 Replies View Related

Global Stored Proc Find And Replace

Jan 29, 2004

Hello all,
Being a relative newbie to large scale MSSQL development, I'd like to try and find out if there is some sort of utility, command or stored proc that I can use to globally change all text within my database's stored procedures.

For example... I would like to change a table name from dbo.xtable... to dbo.ytable... Is this possible?

Actually, I'd be happy with some way to search through all my stored procs to find a specific string (i.e. xtable in the example above).

Any help provided will be greatly appreciated!

Jordan Stradtman

View 7 Replies View Related

T-SQL (SS2K8) :: Enhanced Find And Replace Function

Apr 17, 2014

I'm looking for a way to pull off a complex find+replace within some code, as follows:

@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'

We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...

Rules:

1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).

2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).

View 2 Replies View Related

Find All Special Characters And Replace With Space?

Feb 7, 2014

i need to find and all special characters and replace with space.

Following query works well for me to finding all rows having special char.

SELECT DESC FROM TBL_DESC
WHERE DESC LIKE '%[^A-Z0-9 ]%'

replacing special char with space . i need to remove special chars only.

View 3 Replies View Related

Find And Replace Carriage Return Character

May 30, 2007

Hi I have a text file which I need to import into Access or SQL.

It is 500,000 records which is pipe delimited. The problem is, is that it can contain carriage returns: (square symbol).

Therefore I need to find and replace these characters

Does anybody know of any free ware text file viewers that can do this??

Best Regareds

David

View 3 Replies View Related

Transact SQL :: Find / Replace Numbers In Server

Aug 12, 2015

I have a separate list of calendar years with radiocarbon year equivalents in SQL server but no conversion equation. Most but not all of the data I have is in radiocarbon years. I thought at first I could just link the tables but I don't want the data that is already in calendar years to be linked to this conversion table. Is there any way I can either link the two tables with criteria for which data is linked (Only ages that are in radiocarbon years). Or possibly a way to query all ages that are in radiocarbon years and do something similar to a find and replace with a large list of numbers to change?

View 14 Replies View Related

T-SQL (SS2K8) :: Find And Replace Text For All Sprocs On A Server

Apr 24, 2014

There are plenty of scripts to do this on a per-DB level, but any that will allow me to generate a script for all DB's at once? Mine are split across dozens and it would be much easier to do a loop (using MS_ForeachDB ? )

View 1 Replies View Related

Transact SQL :: Using Regular Expression To Find And Replace Data

Sep 9, 2015

I am writing an SQL query to find an replace data in a column. I have a table that is filled with

C:usersXXXXappdata
C:usersYYYYappda
C:usersZZZZZZappdata

I would like to replace the c:usersXXXXX part with %userprofile%

The end result would be %userprofile%appdata

I know how to dot the replacement in powershell. it's quite easy

-replace "c:users[^]+","%userprofile%"  

Basically how would transfer this into SQL...

View 10 Replies View Related

Newbie Search/replace Case Style Functionality In Derived Column Expression Syntax

Feb 6, 2008



Hi guys,

I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.

similar to this SQL case statement

CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,


the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.

thanks for any assistance you can provide.

regards,

Chris

View 1 Replies View Related

SQL Server 2012 :: Find A Specific String And Replace It With Another Inside Of VARCHAR Field

Aug 14, 2015

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.

View 5 Replies View Related

Find No Case SQL Syntax

Mar 15, 2005

Im looking to strip all non numeric values out of a string at the SQL level in order to compare it to similar user entered data.

Something like

SELECT REReplace(PhoneNumber,"[^0-9]","","ALL") AS NewPN
FROM Numbers
WHERE NewPN = 'UserEnteredNumber'

View 3 Replies View Related

SQL Server 2012 :: Altering All Objects To Find Syntax Errors

Jul 25, 2014

How to alter all objects in database i want to find if can any syntax errors in my database after restoring from sql 2008 to 2012. I Can create as test and drop them but trying to find a way to alter proc , views and functions..

View 4 Replies View Related

SQL Server 2014 :: Find String With Spaces And Replace Them With Other String

Sep 8, 2015

I have following query which return me SP/Views and Functions script using:

select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function

I need its result as:

Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function

I used following

select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?

View 5 Replies View Related

Line Of Code For &"find & Replace/delete&"

Jan 14, 2007

I have mysql type db in which I need computer to search for a key phrase of text that has several hundred occurances inside product table descriptions of products on live site. Once it finds the text, I would like for it to simply remove it, and look for the next occurance of the phrase and repeat the process until all are flagged and removed. I was told their may be a line of sql code that employs such a feature. Can anyone tell me what such a line would be?

View 1 Replies View Related

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

May 20, 2008

Why does the following call to a stored procedure get me this error:


Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.




Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'




The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.


Help me please. Thank you.

View 7 Replies View Related

Incorrect Syntax When There Appears To Be No Syntax Errors.

Dec 14, 2003

I keep receiving the following error whenever I try and call this function to update my database.

The code was working before, all I added was an extra field to update.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'


Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)

Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String

strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text

Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)

cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID

myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()

MasterList.EditItemIndex = -1
BindMasterList()


End Sub

Thankyou in advance.

View 3 Replies View Related

Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?

Mar 31, 2008

Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:

INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName

OR

WHERE f.Name = @FacilityName


My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?

Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?

Thanks!

View 4 Replies View Related

Converting Rrom Access Syntax To Sql Syntax

Sep 23, 2007


Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................

how can you convert it to sql syntax

I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end




Case Statement might be the solution but i could not do it.






Your input will be appreciated

Thank you

View 5 Replies View Related

Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?

Jul 26, 2005

I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved