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


ADVERTISEMENT

Replace All Integers With Continuous 6 Or More Occurrences With X

Oct 4, 2012

I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.

create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO

EXPECTED RESULT:

1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS

drop table t1

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

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

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

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

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

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

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

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
 
Example: AutomobileTypeId (PK, decimal(10,0), not null)

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

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

SQL Server 2008 :: Function To Replace Data In A Column With X Based On LEN Of Data

Sep 4, 2015

I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.

Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval

replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx

My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

View 4 Replies View Related

Replace Data Using Sql

Aug 4, 2004

I have a database of about 300,000 records.
The records were imported from a csv file.
One of the fields is duration.

The data in duration are like ths:
1 second: 0:01
26 minutes: 26:00

If i put the format of the field as time, the data are messed up.
0:01 becomes 1 minute.
26:00 becomes 1 day 2 hours.

I currently have duration as text.

How can i use sql or visual basic to replace all the data so that they can have the format "00:00:00"?
(0:01 becomes 00:00:01, 26:00 becomes 00:26:00)

I need the duration in time format in order to be able to make sum calculations.

I will be doing the same calculations every month so i need the above procedure to be able to execute it every time i need to.

Thank you in advace

George

View 10 Replies View Related

How To Replace Data From One Col With Another Col

Mar 16, 2012

There are 4 tables in a database. such as university, student, professor and worker. university table contains fields named- university_name, student_name, professor_name and worker_name. and student, professor and worker tables contain person_id and person_name.

Now, I want to replace the student_name, professor_name and worker_name data from university table with student table's person_id, professor table's person_id and worker table's person_id.

View 3 Replies View Related

Wildcard For Integers Or Something?

Dec 10, 2001

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.

Does anyone have any ideas here?

Thanks,
Al

View 2 Replies View Related

Adding Integers

Feb 15, 2007

The code below has this line
SET @SOGallons = @ODTGallons

I need it to add the Current value of @SOGallons to the newly selected value of @ODTGallons and set that as the new value of @SOGallons.

I've tried
SET @SOGallons = @SOGallons + @ODTGallons

SET @SOGalTemp = @SOGallons
SET @SOGallons= @SOGalTemp + @ODTGallons

Neither Worked

<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')

OPEN TC1

FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
WHILE @@FETCH_STATUS=0
BEGIN
IF @LT = 'SO'
BEGIN
SET @SplitTest = 1
SET @SOGallons = @ODTGallons
IF @SOGallons > 0
BEGIN
SET @SOGalTest = 1
END
ELSE
BEGIN
SET @SOGalTest = 0
END
IF @SplitTest <> @SOGalTest
BEGIN
SET @SOGalTest = 0
END
END
ELSE
BEGIN
SET @SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>

View 3 Replies View Related

Integers Which Are Greater Than Ones Before Them

Aug 29, 2013

I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.

How do I query just the changes?

Example dataset:

idrating
15
25
35
45
56
66

[code]....

There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:

idrating
45
56
97
118
189

I use Microsoft SQL 2008

View 2 Replies View Related

Count Using Non Integers

Feb 23, 2007

1
2
3
* (unscheduled visit) (should be 3.01)
* (unscheduled visit) (should be 3.02)
Basically when there is an unscheduled visit, it should take the previous visit number and add .01

I am not sure how to count using non integers

Thanks

View 10 Replies View Related

Replace Missing Data

Aug 18, 2006

I am working with a database containing time series data. In many, cases there is missing data. For example, while there might be a value for 2001-01-01T23:00:00, there is none for 2001-01-01T23:0100 (one minute later). I would like to replace the missing data with data from the previous record (if the previous record is the same date). Is that possible with T-SQL?

View 6 Replies View Related

Cannot Add Or Replace Data In SQL Express

Aug 27, 2006

In this project I m using SQL Server 2005 express edtion.I can run select queries but I couldnt run insert and update query.Is there any protection for adding data to the Sql Server express edition ?

thanks.

View 13 Replies View Related

Integers && Dynamic Allocation

Mar 10, 2004

Here is what I am trying to do...
I want to goup "members" togethers in a "group."

A table for members and a table for groups.

each containing coluns...

but inside Groups I would like a column that contains ID numbers for the members that be long to that group.

Being that members can belong to multiple groups - I can not use a GroupID in Members - if so I also need a way of it modular.

Obvisiously I am not a very good DB programmer - but I want the least amount of empty/unused space in my tables.

Hope this makes sense

View 1 Replies View Related

Is There A Function Like SUBSTRING But For Integers?

Apr 14, 2006

I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?

View 5 Replies View Related







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