While Loop To Replace Text - How To Use Set Processing

Aug 12, 2015

I’ve created a script which will do the following.

Update fields in a database which contain instances of an order number. An order number is defined as a 10 digit numeric sting which beings with 998. The first 3 digits of the order number need to change from 998 to 999. There are two types of fields to update: document number fields which may contain 1 instance of the order number and free text fields which may contain multiple instances of an order number.

I created a function which accepts the text to be updated, the text to find and the text to replace it with. The function then loops through the sting for instances of 998. For each instance it finds it checks to ensure that it is at the start of a 10 digit string which contains only numeric numbers – if this is the case then it will update the 998 to be 999.
If the field is free text then it will continue to loop through the string (it will skip forward 10 digits for every order number found) until the end. If the field is not free text then it will exit the script after the first instance found which matches the above criteria, if any.

Need achieving this via set processing and not having to loop through every line. I’ve included the function below and some test data.

--Create the function we will call in order to do the replace
if object_id(N'OrderReplace',N'FN') is not null
drop function dbo.OrderReplace;
go
create function dbo.OrderReplace (
@Textnvarchar(4000),

[code]....

View 8 Replies


ADVERTISEMENT

Simple Text Processing E.g. Regex Search And Replace

Aug 8, 2006

I've got an nvarchar(max) column that I need to transform with some simple text processing: insert some markup at the very beginning, and insert some markup just before a particular regular expression is matched (or at the end, if no match is found).

Since the SSIS expression language doesn't support anything like this, is a Script Component the only way to go? Does Visual Basic .NET provide regular expression matching?

Thanks!

View 13 Replies View Related

For Each Loop And Processing Through DataFlow

May 2, 2008

Greetings!

I am painfully learning SSIS and just when I think I have a better understanding of how things work and go down the path that I should take, I hit a wall. Frankly, discouraging and demoralizing.

I am performing a Select of rows that are then passed on to a ForEachLoop with an ADO Enumerator with a FullResultSet.
Variable mappings for the columns used are:

Variable IndexAccountNumber 0
AccountName 1
AccountAddress 2
CountryCode 3

A ScriptTask in the foreach loop allows me to confirm that I am processing rows.

A DataFlow in the ForEachLoop has in it a DERIVED COLUMN item that allows me to assign the four variable fields above to their respective DERIVED COLUMN.

An attempt to use a Flat File Destination to process the derived columns is being made. As I monitor the execution of the package I notice the following message.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (382)" wrote 0 rows.

Can anyone PLEASE tell me why is it that I am able to see the data being processed through the script task? And yet not see any rows being written to the Flat file. Why?

Any information you may provide would be immensely appreciated.

Thank you and God Bless.

View 6 Replies View Related

Excel Loop Processing Converted To SQL

Aug 24, 2007

I have the following Excel code that I need to convert to SQL. I have looked (a little) at using cursors to do the looping, but I'm not sure how to define the cursors

----------------------


Do While Worksheets("DATA").Cells(sCellRow, 1).Value <> ""
sPartNo = Worksheets("DATA").Cells(sCellRow, 1)

Do While Worksheets("DATA").Cells(sCellRow, 1) = sPartNo

sPartNo = Worksheets("DATA").Cells(sCellRow, 1)


'
' SO #

'
Select Case dCounter

Case 1 To 9

sNextSO = "E" & "0000" & dCounter

Case 10 To 99

sNextSO = "E" & "000" & dCounter

Case 100 To 999

sNextSO = "E" & "00" & dCounter

Case 1000 To 9999

sNextSO = "E" & dCounter

Case 10000 To 99999

sNextSO = dCounter

End Select

'
' PO Line Item
'



Select Case Worksheets("DATA").Cells(dCellRow, 25).Value

Case 1 To 9

sLineItem = "00" & Worksheets("DATA").Cells(dCellRow, 25).Value

Case 10 To 99

sLineItem = "0" & Worksheets("DATA").Cells(dCellRow, 25).Value

Case 100 To 999

sLineItem = Worksheets("DATA").Cells(dCellRow, 25).Value

End Select


Cells(dCellRow, 1).Value = sNextSO



'
' Release #

Cells(dCellRow, 2).Value = dReleaseNum

'

'

' Increment numbers

'

sCellRow = sCellRow + 1

dCellRow = dCellRow + 1

dReleaseNum = dReleaseNum + 1



Loop

dReleaseNum = 1

dCounter = dCounter + 1

Loop

----------------------

Thanks

David Davis

View 8 Replies View Related

How Do I Loop Through Dataset Then Replace?

Aug 23, 2007

How would I loop through the rows of a dataset, then replace certain character in a certain column?
I have a database which has a date field formatted 23/08/2007, I wish to loop through the dataset containing the results from the dataset, change the format of the date to 23.08.2007 then store the value back into the dataSet, which is called 'dataSet', and the table is called 'News'.
Using C# by the way.
Thanks in advance

View 5 Replies View Related

SQL Server 2014 :: Possible To Replace While Loop By CTE?

Aug 11, 2015

Is it possible to replace while loop by CTE?

I will fetch records from table 1 based on tretmentid.
get the count of records
run the while loop
inside loop I will compare the column values with the same table(table 1) for different tretmentid.
if any one row matches will come out from loop and send status .

can it be replaced by CTE ?

View 1 Replies View Related

Replace Text In Text, Char && Varchar Fields All At Once?

Jan 22, 2008

I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.

View 9 Replies View Related

T-SQL (SS2K8) :: Replace Cursor - Convert To Recursive CTE Or While Loop

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

Processing The TEXT Datatype With TSQL

Jul 20, 2005

Hi;I have a table with a TEXT datatype.Its a comment field.Right now the users who put in singlequotes are killing the web frontend.The programmer responsible is fixing this issue but it might be a fewweeks until we get the patch.I would like to write a trigger that whenever this field is updated itwill scan the text for single quotes ( and hard returns
) andextract them.I found some nice string functions in HELP.Will these string functions work with the TEXT datatype in a TSQLscript/trigger?Thanks in advanceSteve

View 1 Replies View Related

Replace Varchar Text

Dec 11, 2007

Hello Guys.

Here is my issue i have email addresses in a column of a table in sql server. Are addreses has changed since and i need to do a mass update of these email addreses i need to replace a few char to reflect this change like ex:

AAAAA@BBB.CCC.Com i need to replace the BBB. part with nothing so that it looks like this AAAAA@CCC.Com.

Please help.

View 4 Replies View Related

Replace Some Text From My Code

Aug 25, 2006

sunil writes "hi,
i have a problem when i updating record in the database. i am trying to insert value ('Company's Director') in the field but i receive an error Incorrect syntax near 's' what do i do for this error.i want to value like as ('Company''s Director').
please solve my problem"

View 1 Replies View Related

Replace One Word With Another In Text Field

Oct 19, 1999

I have a text field that contains multiple words. Is there a way with SQL to relace a single word with another?

for example:

FIELD : CompanyName
DATA : Microsoft Corp.
DATA : IBM Corp.
etc...
DATA : Canon Corp. of America

How can I run a routine to just REPLACE "Corp." with "Corporation" ?

Thanks for your help!
Scott

View 1 Replies View Related

Replace Text (sorry For The Double Post)

Aug 16, 2001

ok Im sure this is simple. what is the command to execute a replace in a select statement

SELECT CUSTOMER.customer_id, CUSTOMER.full_name, CUSTOMER.main_address_1, CUSTOMER.main_address_2
FROM CUSTOMER
WHERE (((CUSTOMER.main_address_1) Like '%road%'))

???Replace all instences of road with RD???
can some one help on this one or even a refrence for research (besides BOL or Technet)

thanks for the help

matt

View 2 Replies View Related

REPLACE Integers With Text Data

Jun 14, 2006

I am working with a database named €œDocuments€? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:

1 = Text
2 = Report
3 = Background
4 = Index

I would like to create a new column named €œDocType€? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:

--CREATE NEW COLUMN €œDocType€? WITH VARCHAR DATATYPE

ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL
Go

--UPDATE NEW COLUMN WITH CAST STRING

UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))
Go

But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:

--REPLACE NUMBERS WITH LETTERS

UPDATE Fulldocuments REPLACE (DocType,"1","T")

Which produced an error message: €œIncorrect syntax near 'REPLACE'.€?

Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:

UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")

Which produced the same error message: €œIncorrect syntax near 'REPLACE'.€?

I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.

View 3 Replies View Related

How To Replace Spacebar In Text With Condition

Mar 20, 2008

need help with spacebar
i have table with names i need to replace the spacebar with "-"
only where val=2
my table

before

fname val
-------------------------------------------
aaaa bbbbbb 1
xx oihjhjhjh 2
sspppp pppll 2
ooooooo ne 1
xxoihjhjh jhkkhhk 2


after only where val=2

fname val
-------------------------------------------
aaaa bbbbbb 1
xx-oihjhjhjh 2
sspppp-pppll 2
ooooooo ne 1
xxoihjhjh-jhkkhhk 2



TNX

View 11 Replies View Related

Stored Procedure - Replace Null With Text Msg

Jan 27, 2006

Hi

I'm trying to create a stored procedure using the northwind db which will do the following:

SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock
FROM Products

However, where UnitsInStocks = 0 I would like the words "Sorry, out of stock" to appear. I will then call this from an ASP page.

Can anyone help please?

Cheers



Woolly

View 7 Replies View Related

Replace-type Function For Text Datatype

Jul 20, 2005

I have a table that has a Text datatype column that has gotten somegarbagecharacters in it somehow, probably from key entry. I need to removethe garbage, multiple occurances of char(15). The replace functiondoes not work on Text datatype. Any suggestions?

View 3 Replies View Related

Replace An XML Value In A Text Column In SQL Server 2005

Sep 27, 2007

I have a SQL Server 2005 database that has a table with a TEXT column. This TEXT column has XML data in it. The length of the XML data in each record in the table is about 700,000 characters. What is the quickest most efficient method to replace a nodes text with another value? I.E., <LogoLarge>aasdfasdfaasadfasdfsdfasdfadsf</LogoLarge> with <LogoLarge>a</LogoLarge>. This table has about 2 million records. Thanks in advance for your help.

View 2 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

SQL 2012 :: TRIM And / Or REPLACE In Editing A Lot Of Text Fields

Dec 22, 2014

I have a text field with about 17,500 rows I need to edit from for example: '1.90X .90' to '1.90X 0.90'? The numbers are various but follow the basic format albiet they may vary to say '22.78X .40'

View 8 Replies View Related

Transact SQL :: REPLACE Part Of String With Different Piece Of Text

Apr 20, 2015

I have a string column in a DB where it's values contain the following midway through the string ([DOCUMENTGUID] is a uniqueidentifier that is different for each row):

<a href="../downloadDoc.aspx?dg=[DOCUMENTGUID]" target="_blank">

I would like to replace this part of the string with a different piece of text.

I know that I should be using PATINDEX and REPLACE functions but I don't know how to utilise.

View 4 Replies View Related

Replace Carriage Return And Line Feed In A Text Column?

May 18, 2004

Hi,

I've a text column (text datatype) that contains carriage return and line feed.

Syntax-wise, how can I replace these by a space?

Thanks.

View 1 Replies View Related

SQL Server 2008 :: Stored Procedure Text String Replace

Apr 16, 2015

We have a legacy database that have hundreds of stored procedures.

The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.

I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.

I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?

View 2 Replies View Related

Loop Through All Text Files

May 11, 2008

Hi All
I have a situation in my SSIS Project where I have a folder in which contains 10 text files, what I need to do Loop through all these files and select the data from those text files and insert them in SQL Database table. I will be able to get the names of those file using For Each Loop Container but I don’t have idea how to insert the data in SQL table using for each loop container. I don’t want to use multiple data flow task. Any help will really appreciate
Many thanks

View 1 Replies View Related

Replace Text/data In One Table With Corresponding Text/data From Another

Jan 10, 2006

Hi -

I've never used SQL for anything but simple copies and queries, and now I need to do something that's probably simple, as well, but I don't know how to start.  I need to update values in a text field with new values from another table.  I can do it individually with an update statement (Set [field1] = 'newvalue' where [field1] = 'oldvalue').  But I have 400 different values and a bunch of different tables that need to be updated, and can't imagine that's the only solution.  I can make up a simple table that will have all the old values in one column, and the new values in the next.  I need a statement that look at the old value, then fetch the new value from the table I made, and replace the old value with the new.

I've looked at various help files and tried to search for a solution elsewhere, but I'm not coming up with anything.  I guess I just don't know where to look.  Thanks in advance for any help you can give me.

Rebekkah

View 5 Replies View Related

Argument Data Type Text Is Invalid For Argument 1 Of Replace Function.

May 14, 2008



Hi There,

Could someone please tell me why I am getting the above error on this code:

select (replace
(replace
(replace
(replace (serviceType, 'null', ' ')
, '<values><value>', ' ')
, '</value><value>', ',')
, '</value></values>', ' '))
from credit


serviceType (text,null)

Thanks,
Rhonda

View 1 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View 18 Replies View Related

ForEach Loop Or For Loop??

Feb 23, 2006

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

View 1 Replies View Related

DTS PROCESSING

Aug 3, 2001

I have a DTS that imports data from an orcle database into SQL Server.
Doesn't the processing mostly occur on the SQL Server, not on the
oracle database from which the data is being imported?
The oracle database is vendor provieded and they are saying
our SQL Server DTS package is killing their server.
Any insight is appreciated.
Thanks

View 1 Replies View Related

XML Processing

Mar 27, 2008

Hey All,

I've got a process that creates records in my database based on XML input that I've gotten. What I am doing it giving this XML to a stored procedure to handle a specific task, then modify the XML and send it to the next stored procedure.

For instance, the XML could hold header records with detail records, I would first send the XML to a stored procedure that creates the header records, then updates the XML so the XML now knows the identity values of the header records I have just created, and then send the XML to the next stored procedure to create the details for those headers.

All works great and fine, but I have a problem with writing the identity values back to the XML. It seems I can only change one item in the XML at a time and thus need to loop this. For many records this really takes a long time.

Here is some sample code of what I'm doing (please excuse any typos, this is a simplified version of the code) :

declare @lvSeq numeric(15)
declare @lvRowNo int
declare @lvNumRows int

insert into myHeaderTable (
recid, recdesc
) select
ref.value('@recid', 'nvarchar(25)') recid,
ref.value('@recdesc', 'nvarchar(250)') recdesc
from @pXML.nodes('//headers/header') R(ref)

select @lvRowNo=1, @lvNumRows = @pXML.value('count(//headers/header)', 'int')
while (@lvRowNo<=@lvNnumRows) begin
select @lvSeq = recseq
from myHeaderTable
where recid = @pXML.value('//headers/header[position()=sql:variable("@lvRowNo")]/@recid)

set @pXML.modify('replace value of (//headers/header[position()=sql:variable("@lvRowNo")]/@recseq with sql:variable("@lvSeq")')

select @lvRowNo=@lvRowNo+1
end


Obviously I am looking for a better way to update the XML with the sequences. The insert takes a second, the loop takes minutes with large XML sets. I guess MSSQL is searching the whole XML to find the item to update.

It would be nice if I didn't have to loop through the XML. One solution I was thinking off is to store the XML in a temporary table with a single record per header item. Then I could do the modify in one go and recreate the XML by simply selecting the contents of the temporary tabel. I have no idea if this is possible.

So something like this:

select
ref.value('@recid','nvarchar(25)') recid,
ref.value('.','XML') XMLData -- this gives an error
into #TMP_XML
from @pXML.nodes('//headers/header') R(ref)

insert into myHeaderTable (
recid, recdesc
) select
recid,
ref.value('@recdesc', 'nvarchar(250)') recdesc
from #TMP_XML CROSS APPLY XMLData.nodes('/header') R(ref)

update #TMP_XML
set XMLData.modify('replace ....')
from myheadertable
where #TMP_XML.recid = myheadertable.recid

-- recreate XML here, not sure how....

View 1 Replies View Related







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