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
ADVERTISEMENT
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
Aug 16, 2005
I can't remember if I posted this already or not:
CREATE PROCEDURE sp_space @sortbyrows bit=0 AS
SET NOCOUNT ON
select cast(object_name(id) as varchar(50)) AS name, sum(CASE WHEN indid<2 THEN rows END) AS rows,
sum(reserved)*8 AS reserved, sum(dpages)*8 AS data, sum(used-dpages)*8 AS index_size, sum(reserved-used)*8 AS unused
from sysindexes with (nolock) where indid in(0,1,255) and id>100
GROUP BY id with rollup
ORDER BY CASE WHEN @sortbyrows=1 THEN sum(CASE WHEN indid<2 THEN rows END) ELSE sum(reserved)*8 END desc
Usage:
EXEC sp_space --show stats sorted by reserved space size
EXEC sp_space 1 --show stats sorted by row count
It basically displays the results of sp_spaceused, but broken out by each table, and it rolls it up to a grand total. Naturally, for the most accurate results you should run DBCC UPDATEUSAGE before running this sproc.
View 10 Replies
View Related
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
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
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
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
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
May 19, 2014
I am working with a large amount of text data in a single column. I am in the process of pumping that column of data into a delimited text file. The data in the column has new line characters that I need to remove. I need to data to be in a single line in the text file.
So far the things that I have tried are not working. What I try for removing the new line characters?
View 3 Replies
View Related
Jul 16, 2014
CREATE TABLE [dbo].[instructions](
[site_no] [int] NOT NULL,
[instructions] [text] NULL
)
Select top 3 * from instructions
Output
Site_no Instructions
20 Request PIN#510 then proceed
21 Request PIN#987 if wrong request name
22 Request PIN#688 allowed to use only numbers
All text instructions start with “Request PIN#XXX” also after that the text are different for every site_no
I need insert in all site_no rows and after the “Request PIN#XXX” the text “and codeword” keeping the current rest of text
How can I set e REPLACE CAST sentence using something LIKE PIN%%%%
To get these type of results
site_no instructions
----------- ----------------------------------------------------
20 Request PIN#510 and codeword then proceed
21 Request PIN#987 and codeword if wrong request name
22 Request PIN#688 and codeword allowed to use only numbers
View 4 Replies
View Related
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
View Related
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
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
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
Jun 11, 2014
I have an database that is housing a path used to locate an external file. This application was written many years ago and I am now trying to bring the files into the database as a VARBINARY.
The table is holding the path like this "/folder/folder/file"
I am trying to convert that path to "folderfolderfile"
In my Select statement I have
SELECT ProdID, REPLACE (PATH, /, ) FROM dbo.blahblah
The problem is that I can't figure out to make SQL understand that "/" is the character I want to replace.
View 2 Replies
View Related
Mar 23, 2015
I have data like below
Potter, James J
Williams, Ted R
Allen, Gary G
I want to remove Middle Name from the output
Potter, James
Williams, Ted
Allen, Gary
My Query:
SELECT
CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN
REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '')
ELSE Supervisor END AS NewSupervisor from data d
However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"
Do we have any way to say sql to replace after certain index?
View 3 Replies
View Related
Jul 2, 2014
Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400)
declare @str nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor
[Code] ....
View 9 Replies
View Related
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
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
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
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
Oct 30, 2003
Hi,
I have a table with a field called productname, and it has about 5000 rows, and within that about 1000 have a productname that has 'NIB' in the name, ie "My Product NIB DVD" and I have been asked to replace 'NIB' with 'New' ie "My Product New DVD" Can I do this in SQL using an Update statement? Or do I have build something in maybe asp.net to use a replace function to change the name.
Thanks
View 9 Replies
View Related
Aug 10, 2004
I am using MSDE. In this I have a field that contains a desciption. Prior to saving to the db, I replace all vbcrlf's to <br />'s. That works fine when displaying in HTML, but when I display it in a datagrid, I want to replace the <br />'s with vbcrlf's.
I thought I might be able to do it with a replace function in my SQL query, something like "Select ID, replace(Description, '<br />', vbcrlf) as Description". This produces an error that vbcrlf is not a fieldname. So I tried "select replace(Description, '<br />', 'xxx') as Description", and while this did not create an error, neither was the text replaced.
Am I not using the replace function correctly? Is there another way in which I might accomplish the task?
Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database=dbname"
Dim CommandText As String
'Command text is greatly abreviated for this discussion.
CommandText = "Select ID, replace(Description, '<br />', vbcrlf) as Description"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet()
Dim dv as new dataview()
myCommand.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
...
...
...
View 3 Replies
View Related
Mar 28, 2006
I have a need to evaluate a parameter to use like keywords. (Not my server, so indexing is not available.) I can get the first scenario to work, but wanted to see about getting the syntax so I wouldn't have to exec the statement. If I run the first execute statement, I get three rows (accurate). If I run the second statement, I get no rows. Anybody help me out with the syntax? I've tried so many variations, I'm lost. Many thanks.
declare @key as varChar(50), @sql as varchar(1000)
set @key='flow afow'
set @sql='select myID from myTable where [title] like ''%' + replace(rtrim(ltrim(@key)),' ','%'' or [title] like ''%') + '%'''
exec(@sql)
select myID from myTable where [title] like '%' + replace(@key,' ', '%''' or [title] like '''%') + '%'
View 2 Replies
View Related
May 17, 2002
I'm trying to replace some text in a field. It looks something like this:
63.73 Avail %= 36.27 Used space MB = 2609.34375 Free space MB = 1485.34765625
I only want the 63.73 number at the beginning. How do I put a wildcard to replace everything after it, starting with 'Avail...'?
View 4 Replies
View Related
May 12, 2004
I want to perform Replace(searchstring, oldstring, newstring) like function in T-sql as same as in VB..
is there existing one or any other user-defined function?
thanks...
View 2 Replies
View Related
Apr 16, 2007
Is it possible to replace 4 different values into one alias column?
ie REPLACE(EligStatus, '0', 'Verified') AS VRFD
..now I need to replace 3 other values with words but i want to keep them in the same VRFD column so I can add it to the report layout.
or is there a better way to do it?
I can't set up a linking table because nowhere in the DB are the values linked to the actual status.
Thanks in advance,
Mark.
View 4 Replies
View Related
Jun 21, 2012
I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<
View 9 Replies
View Related
Dec 12, 2014
There is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]
[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000
For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400
The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...
DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';
SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]
SET @Total='select '+@formula
EXECUTE sp_executesql @Total;
PRINT @Total;
View 3 Replies
View Related
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
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
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
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