How Do I Handle REPLACE A Particuler Matched String Within An NTEXT Column In SQL Server?

Mar 27, 2008

Hi!
I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error

I tried following

UPDATE CaseTypeDefs SET definition = replace(LTRIM(RTRIM(definition)), '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]>', '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]>')


But this is producing following error

Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.

For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.

Need help, how to do it?.


Thanks In Advance

Devloper
Anil Kumar Dwivedi

View 4 Replies


ADVERTISEMENT

Replace A String In An NTEXT Field In Sql Server

Oct 15, 2006

I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.

View 8 Replies View Related

How To Handle Text, Ntext Data Types Of SQL Server

Jan 8, 2004

i have created asp.net page, one feild of this page text area.
when i insert some text through this page in "text feild" of SQL server on few words of this feild cut and inserted to text feild of SQL server but all text that i have written in text area feild.

can u please help me how to handle so that i can all text in text feild of SQL server data type text/next.

View 2 Replies View Related

Replace A String Column With 'Y' Or 'N'

Nov 12, 2007

Hi,

I am trying to replace a string column with 'Y' or 'N' in my SSRS report. I tried the following expressions, but no one works for me.


=IIf(Fields!ExpectedWords.Value Is Null, 'Y', 'N')

=IIf(Fields!ExpectedWords.Value = '', 'Y', 'N')
=IIf(IsDBNull(Fields!ExpectedWords.Value), 'Y', 'N')


Would you please tell me what's wrong? Your suggestions would be appreciated.

Thanks alot.

View 3 Replies View Related

Replace String In A Table - Update Column Using Variables

Jul 3, 2014

Trying to replace a string in a table and it is not working the path can be like OM-WD08-1 reportData.raw

USE Config

DECLARE @OldPath varchar(30), @NewPath varchar(30)

-- Initialize the variable
SET @OldPath ='OM-WD08-1';
SET @NewPath ='AA-PC';

UPDATE AnatomyConfigs
SET Path = REPLACE(Path,@OldPath,@NewPath) WHERE Path IS NOT NULL
AND Path LIKE @OldPath
GO

View 3 Replies View Related

Derived Column Logic To Replace Empty String With Null?

Nov 7, 2006

Ok.. so I have a fixed position data feed. I read the file in as just whole rows initially, process a specific position and evaluate a conditional split to determine direction of the file for proper processing (file contains multiple recors with different layouts). This all works fine. I then use the derived column feature to process all the columns.

Most of the columns are as simple as SUBSTRING(RecordData,1,10) for example to get the first column. This all works for string data. I then have a column that is a date field. The problem occurs that the code SUBSTRING(RecordData,20,10) returns either a date or empty set of data if no date was in the original file. When this gets sent to the OLEDB connection (SQL Server 2005) into the date field it fails. If the record has a date it works, but if it is empty it fails the insert.

I tried to replace empty strings with NULLs with this code. REPLACE(TRIM(SUBSTRING(RecordData,20,10)),"",NULL(DT_WSTR,10)). This does not work. So my question is how do I bring a date field from a fixed flat file into a SQL datetime field using a derived column? More specifically is how do I set it to NULL if its empty data? When I use the above code it inserts all the rows from the file, but it sets all rows to NULL not just the empty ones.

Thanks.

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

How To Replace Char In Ntext Field

Jun 16, 2006

I need help on replace char in ntext data type

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

View 1 Replies View Related

Use Replace() In An Update In A Large Ntext

Apr 21, 2008

I've got a table that I have to update in preparation for our environment move (2k to 2005 SP2). The developers that designed the application created a table called schemas, which holds the contents of an XML file inside of an ntext field named Data.I need to parse through the field and do a find/replace to replace all instances of www.site.com with www7.site.com. It's all over the place in the file. The problem is, that the datalength() of each of the fields (there are 2 rows) are above 15000.normally, I'd run something like this:update schemas set data=replace (cast(Data as varchar(max)),'www.site.com','www7.site.com') where data like '%www.site.com%'Smaller columns it works great - but it won't work on these because they're too big (the update will chop anything beyond the varchar(max) value). I could do it manually, but this DB will be refreshed from production on a weekly basis and I'd like to script as many of the environment changes to the DB as much as possible. Any ideas?

View 1 Replies View Related

Replace() On Data Type Ntext - How To?

May 29, 2006

Hello guys,

I would like to execute a t-sql query and replace a string by another in all records.

I did this :

UPDATE myTable
SET myColumn = replace(myColumn,'old string','new string')

The error I get is :
Argument data type ntext is invalid for argument 1 of replace function.

Thank you very much for any help!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

View 5 Replies View Related

SQL Server Compact Edition Ntext Fails With String Over 4000 Characters

Jan 25, 2008

This is with SQLCe NET 3.5.0.0 running on Windows Server 2003 or Server 2008, not on a Windows mobile operating system.

The following code fails with ntext entries above 4000 characters:

Dim cn As New SqlCeConnection(ConnectString())
If cn.State = ConnectionState.Closed Then
cn.Open()
End If
Dim info as string

info = "This is lengthy text".PadLeft(4200)
Dim cmd As SqlCeCommand

strSQL = "create table testTable ("
strSQL &= "docType nvarchar (50) NULL, "
strSQL &= "docFlag nvarchar(10) NULL, "
strSQL &= "docData ntext NULL, "
strSQL &= " )"

cmd = New SqlCeCommand(strSQL, cn)
Try
cmd.ExecuteNonQuery()
Catch sqlexception As SqlCeException
MessageBox.Show(sqlexception.Message & vbNewLine & strSQL, "Table Error 7", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Catch ex As Exception
MessageBox.Show(ex.Message, "Table Error 8", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
cn.Close()
End Try
If cn.State = ConnectionState.Closed Then
cn.Open()
End If

'---- insert a row into the testTable

strSQL = "INSERT INTO testTable ("
strSQL &= "docType, "
strSQL &= "docFlag, "
strSQL &= "docData, "
strSQL &= ") "
strSQL &= "VALUES ("
strSQL &= "@docType, "
strSQL &= "@docFlag, "
strSQL &= "@docData, "
strSQL &= ")"

Try
cmd = New SqlCeCommand(strSQL, cn)
cmd.Parameters.AddWithValue("@docType", "a type")
cmd.Parameters.AddWithValue("@docFlag", "a flag")
cmd.Parameters.AddWithValue("@docData", info)
cmd.ExecuteNonQuery()
Catch sqlexception As SqlCeException
MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Catch ex As Exception
MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
cn.Close()
End Try
End If

Changing the cmd.Parameters as follows works:

Dim paramdocData As SqlCeParameter
Try
cmd = New SqlCeCommand(strSQL, cn)
cmd.Parameters.AddWithValue("@docType", "a type")
cmd.Parameters.AddWithValue("@docFlag", "a flag")
paramdocData = cmd.Parameters.Add("docData", SqlDbType.NText)
paramdocData.Value = info
cmd.ExecuteNonQuery()
Catch sqlexception As SqlCeException
MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Catch ex As Exception
MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
cn.Close()
End Try

Thanks to the following Microsoft ReadMe for the above suggestion. See:
http://download.microsoft.com/download/f/7/2/f72ebbf8-4df1-4800-b4db-c2405c10d937/ReadmeSSC35.htm

View 3 Replies View Related

How To Handle 1 To Many Column Insert In Sql Server

Nov 19, 2007

Hi,
   I have a table Called Actcodes and has 2 columns Name and Description...
 And i want to insert the data from this table called PlanDBF to ActCode..
and this my insert statement...
 INSERT INTO Statements..AscActCodes
(
Name,
Description
)
SELECT
ACT_CODE1,
ACT_DESC1
ACT_CODE2,
ACT_DESC2,
ACT_CODE3,
ACT_DESC3,
ACT_CODE4,
ACT_DESC4,
ACT_CODE5,
ACT_DESC5,
ACT_CODE6,
ACT_DESC6,
ACT_CODE7,
ACT_DESC7,
ACT_CODE8,
ACT_DESC8,
ACT_CODE9,
ACT_DESC9,
ACT_CODE10,
ACT_DESC10,
ACT_CODE11,
ACT_DESC11,
ACT_CODE12,
ACT_DESC12,
ACT_CODE13,
ACT_DESC13,
ACT_CODE14,
ACT_DESC14,
ACT_CODE15,
ACT_DESC15,
ACT_CODE16,
ACT_DESC16,
ACT_CODE17,
ACT_DESC17,
ACT_CODE18,
ACT_DESC18,
ACT_CODE19,
ACT_DESC19,
ACT_CODE20,
ACT_DESC20
FROM
PlanDBF

 each actcode and its description is gonna be different and so i am not sure how to do this 1 - many column insert.
 
Any help will be appreciated.
Regards
Karen

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

SQL Server 2012 :: Best Way To Handle Like Percentage On Column Too Large For Index

Sep 18, 2015

We have a table to 100M rows and up until now we were fine with an non clustered index a varchar(4000) because we never went above 900 bytes (yes it is a bad design).We have the need to support international character sets now so the column was updated to nvarchar(4000) and now we have data past the 900 byte limit.

The data is long, seems useless but is needed by the business and they need to be able to search "where bigcolumn like 'test%'". With an index, even with a huge amount of data, it was 'fast'. Now of course without an index it is unusable. The wildcard is always at the end of the search. I made a full text index on the column and basic queries such as: select * from ourtable where contains(bigcolumn, 'AReallyLongStringofTextHere') works fine unless there is a space in the data. We loose thousands of returned rows because of spaces in the data.

I have tried select * from ourtable where contains(bigcolumn, '"AReallyLongStringofTextHere that includes spaces"') but not all of the data is returned. I get 112 rows with the contains statement. The table scanning statement of "select * from ourtable where bigcolumn like 'AReallyLongStringofTextHere that includes spaces%' returns 1939 rows.I understand that a full text index is breaking the long string up since it contains spaces. Is there a way to retain the entire string as 1 index entry or is there a way to fix my query to return all of the rows?

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

SQL Server 2012 :: Get Unmatched And Matched Values From Two Tables

Apr 10, 2015

I have two tables category and lu_category.

The category table has columns [CategoryId], [CategoryName], [TotalCategoryRiskScore] and the lu_category has columns [CategoryId], [CategoryName].

I want a sql query that will list all values from lu_category table and category table and if a categoryid is not available in lu_category table but available in category table,
i need that too in the result.

Below is the screenshot of the data and my desired output

I have attached the data as spreadsheet.

View 6 Replies View Related

Replace Data In SQL Server Table Column

Apr 14, 2004

What is the correct syntax to replace a field data nvarchar(50)

Current data = 0020-10-02
Change = 2003-10-02

Thank you in advance.

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

How To Handle Connection String When Using Tables From Multiple Databases?

Mar 10, 2004

I have a query that involves tables from 2 different databases. Using the query analyzer I can construct my query and it works great. Now I am trying to implement the query into my code and I am confused about I handle the connection string(s) since I am now using 2 different databases.

Can anybody help?

Cheers,
AzF

View 2 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

SQL To Replace String

Jul 26, 2002

Hi,
I need to write a single replace sql as folows:

I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*'

I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5'


any help would be appreciated

Thanks

View 3 Replies View Related

SQL To Replace String

Jul 26, 2002

Hi,
I need to write a single replace sql as folows:

I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*'

I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5'


any help would be appreciated

Thanks

View 1 Replies View Related

SQL To Replace String

Jul 26, 2002

Hi,
I need to write a replace sql in TSQL as folows:

I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*'

I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5'


any help would be appreciated

Thanks

View 3 Replies View Related

String Replace

May 7, 2008

I've got a simple table with a column called html. In the html column I need to replace all occurances of <BR> with <br />

Can I do this with an update?

Thanks

View 1 Replies View Related

How To Replace String

Jun 3, 2014

if I have this string "ABCD test love", is there a way I can get rid of all text after "test" so that it becomes "ABCD test".

View 2 Replies View Related

Replace Char At End Of String

Mar 2, 2000

Hi,

I have a table where this one column(varchar) has rows of data with a period at the end of the string. Is there any function I could use to remove all the periods?

For eg:
bhjio.
shtpl.

should become:
bhjio
shtpl

Thanks

View 1 Replies View Related

MS SQL 2000 String.replace

Jul 12, 2007

Hello

with the following query :

SELECT words FROM T1

i get :

A,B,C

how can i get

A > B > C

something like String.Replace(words , ',' , ' > ')

thank you

View 3 Replies View Related

Update String/replace?

Jan 15, 2008

Hi experts,

I need to do an update on a field which will update 3 characters within a string. Right now I have '000111000333'. I want to replace '000' starting from the 7th character space with '222'. The first set of zeroes will remain unchange. I want the final outcome to be '000111222333'. How can I do this?

I have tried searching this forum and could not find anything. the closest thing I can find is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81890#360936.

Thanks for the help!

View 2 Replies View Related

Analysis :: Replace String In Mdx?

Jun 25, 2015

I have employee members like

"firstname , secondname" in my dimension

i want remove , and display it like "firstname  secondname"

i can do the same in sql server but i have to do it in mdx

View 4 Replies View Related

Column Size For Ntext

Aug 11, 2005

Hi,

Please help me to find out the ntext column size in a table.

Thanks in advance

Regards

Karthik

View 2 Replies View Related

Best Way To Handle Populating Column?

Jul 2, 2014

Have two tables: table1 and table2

Working with two main columns in both tables: D_ID and C_ID.

Table1 has D_ID populated and C_ID is not.

Table2 is a cross reference table with both D_ID and C_ID values within.

Looking for best way to populate C_ID in table1 from C_ID values in table2 where table1.D_ID = table2.D_ID.

There are too many values to do a case in stored procedure which has been my best practice. Trying to up my game with using SSIS 2012 as well.

View 1 Replies View Related

How To Replace Integer With A String In A Query

Apr 15, 2008

Hi all, In a sql query I need to replace 0 (zero) with "Not rated" ...Can some one help me to do this.In short: how to replace a integer value to a string in a query? is it possible?Thanks for the HelpRamesh 

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







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