Replace Invalid XML Characters Using SQL Query

Apr 21, 2008

Hi,
I am populating a dataset in .net with output from sql 2005 database. One of the columns in the table is a 'varchar(max)' type. This dataset is then converted to XML using WriteXml and written to a .xml document. But due to the presence of invalid characters, this process errors out.
Is there any way using which these invalid characters can be replaced at the database level itself when querying on the table?
The error that is produced is as follows:
'', hexadecimal value 0x1C, is an invalid character. Line 32201, position 924.

Thanks,
Nisha

View 14 Replies

Replace Characters In A Query

Dec 3, 2002

Hi All,

I have a column in one of my tables and I need to display only Alphabets and Numbers of this column, preferably in one single query.

Eg: Column Value --> This is 4th value with no *, &, # symbols.

I want this to be displayed as

Thisis4thvaluewithnosymbols

(All spaces and characters other than alphabets and numerics are gone)

Pleas help.

Thanks

View 1 Replies View Related

Transact SQL :: Replace Column Value From ASCII Characters To Non ASCII Characters In Table?

Oct 22, 2015

I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.

Note – values in column must be same

View 10 Replies View Related

Replace Bad Characters In Table

Dec 10, 2013

I've got a lot of %20 characters in my tables, and need to remove them however I get the error:

Argument data type ntext is invalid for argument 1 of replace function.

My Code:

UPDATE cmsPropertyData
SET dataNtext = REPLACE(dataNtext,'%20','')
WHERE cmsPropertyData.contentNodeId = 1151

View 2 Replies View Related

T-SQL (SS2K8) :: Replace All New Line Characters

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

[RESOLVED] Replace Characters In Record

Mar 18, 2007

Still new to SQL and having fun learning it. Here's my latest question.

I have a table 'Fields' that has a column named 'label'.
In label, I have values like 'Total Number of Orders'
I want to replace the text Number with a #. How can I do that for all records that contain 'Number'?

Thanks

View 2 Replies View Related

Replace Characters On Condition In Table And Cells

Apr 14, 2008

need help

replace characters on condition in table and cells

but only if

if i put number or 1 , 2 , 3 , 4 above the cell of the eployee for example( employee id=222 name =bbbb day1)

i replace characters with '0' and '#'

and it must work dynamically AND replace ONLY THIS characters

table before the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 1 3
111 aaaa 1 A C
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 2
555 eeee 1 B

table after the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 0 0
111 aaaa 1 # #
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 0
555 eeee 1 #

tnx FOR THE HELP

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

Replace Characters On Condition In Table And Cell

Apr 11, 2008

need help
condition 1
replace characters on condition in table and cells
but only if
how to do it - if i put * (asterisk) like in employee 111 in day1 - the the upper characters the (A S B)
i replace characters with '-'
and it must work dynamically
condition 2
replace characters on condition in table and cells
but only if
if i put number or 1 , 2 , 3 , 4 above any cell for example( employee id=222 name =bbbb day1)
i replace characters with '0' and '#'
and it must work dynamically
table before the replace

id
fname

val

day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555

111
aaaa

2
A
S
B
e
t
y
R
Y
M

j
o
p

111
aaaa

1
*

*

*

*

222
bbbb

2
1

1

222
bbbb

1
A
-
-
-
B
-

333
cccc

2

333
cccc

1

444
dddd

2

3

4

444
dddd

1

-
-
C

C

555
EEE

2
A
G
C

555
EEE

1
*

table after the replace

id
fname

val

day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555

111
aaaa

2
-
-
-
-
-
-
-
-
-

-
-
-

111
aaaa

1
null

null

null

null

222
bbbb

2
0

0

222
bbbb

1
#
-
-
-
#
-

333
cccc

2

333
cccc

1

444
dddd

2

0

0

444
dddd

1

-
-
#

#

555
EEE

2
-
-
-

555
EEE

1
null

tnx for the help

View 11 Replies View Related

Removing Invalid Characters

May 6, 2008

here is my data
112344*100
23*34444

I want to remove * and make sure the length is 11 digits and and add leading 0's

Desired data
00112344100
00002334444

How will i be able to achieve this..

View 2 Replies View Related

SQL Remove Invalid Characters

Sep 10, 2007

I have data with invalid character in my source table. I'd like to remove the replace/remove the invalid characters. What function should i use?

e.g.,

0233��
20116267 ��{ ��E��

I'd like to get only
0233
20116267.

I'd appreciate your inputs!

Thanks,
OM$

View 6 Replies View Related

Transact SQL :: Replace Special Characters And Space Except Dash

Sep 18, 2015

I want to replace special characters and space except '-' Tried with Pathindex and function but not getting expected results.

Ex: Input kjkdjfdf-234#kei$ ewiw

output: kjkdjfdf-234keiewiw

View 6 Replies View Related

Prevent Invalid Characters From Being Entered

Sep 24, 2006

Hi,

I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle.

In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data.

If this is not possible, is it viable to remove the character after insert and still send the email with the required info?

Any one any ideas?

Thanks

View 3 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Characters With Single Character In A Column?

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

T-SQL (SS2K8) :: Replace Multiple Characters Based On Table Values

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

Transact SQL :: Replace Special Characters In ORACLE Or SERVER 2012?

Jun 8, 2015

I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?

View 5 Replies View Related

Transact SQL :: Replacing Invalid Characters In A Field

Oct 6, 2015

When we are getting data in a table and we want to replace characters with other characters. For example, We have a table with a street address, and there are numerous ascii character values we want to review and replace if they exist. We were looking at using a table with 2 columns, 1 containing each ascii character value the other it's preferred replacement value. Then updating the street address searching through each ascii character and replacing it if needed. Currently, we are running it through a looping process searching each individual address for each ascii character, and updating it.

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

Query Fails With Invalid Column Name But Succeed As Sub-query With Unexpected Results

Sep 22, 2015

-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"

set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo

[code]....

This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?

View 2 Replies View Related

REPLACE Used In Query

Feb 21, 2004

I'm not sure how to use REPLACE here. The Query following REPLACE returns a string whose format is A B C D and I'm trying to convert it to 'A','B','C','D' I'm doing something wrong because query analyzer doesn't like something about the way I've written this.

WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''')

Entire SPROC
-------------------------------------------------------------------------------------------------
CREATE PROCEDURE _premium_BSH (@Portfolio_Name NVarChar (50), @UserID int, @Symbol VarChar (1500)) AS

SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin],
a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = '2003 Q3')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal
GO

View 8 Replies View Related

Replace Query? Possible?

Sep 29, 2004

Is it possible to make a replace query in SQL?

So Find What, Replace With

I have a table with email addresses. But now for more than 500 people it must be changed from user@company.nl to user@COMPANY2.nl

Is this possible?

Thanks in advance!

View 8 Replies View Related

Replace Query

Feb 20, 2008

I have table data field like this. (type text)

TAZ1
-----------
SM1-01
SM1-04
SM1-03
....

SM10-18
SM10-21
SM10-05
SM13-06
SM13-07
....
..
SM13-09
SM14-07
SM14-08
...
...

I want to write replace query that when replace query run
it write all data to TAZ2 field like this

TAZ2
-----
101
104
103
...

1306
1307
...
..

1407
1408
...
..

i.e. replace MS and - (hyphen) and write data (only number, digit)
to TAZ2 field live above ?

Thanks and regards
Mateen

View 9 Replies View Related

Select Query With Replace

Jan 8, 2008

hi,i have entered records using replace functionnow i have to retrieve that records, i have replaced <'> with <`> character, how to write the query to get a record replacing again <`> with <'>

View 2 Replies View Related

Trying To Write A Replace Query

Aug 24, 2001

I can't figure out how to replace a single digit entry in my database with a new digit. i want to replace in the alarms.severity all values of 4 with a new value of 2. For some reason the script I wirte won't get past the digit 4 in the statement. Anyone have a script they can let me try?

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

Replace And Date/month Query

Mar 19, 2008

SELECT CRDATE, RCTNCCRD
FROM creditcardtable
WHERE (RCTNCCRD <> '') AND (NOT (ISNULL(RCTNCCRD, '999') = '999')) AND (RCTNCCRD NOT LIKE '%x%')
ORDER BY CRDATE DESC

i want to convert any credit card numbers in creditcardtable to an obfuscated format such as:

4332 3423 5423 5428

And convert it to

XXXX XXXX XXXX 5428

HOWEVER, it will only do it if the order is older than 3 months old. Only 90 days/3 months max

how can i do that?
when i use where crcdate<90...it gives me error..
can anyone help me?

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

Writing A Simple Replace Query.. Another Question

Mar 21, 2006

http://forums.devshed.com/ms-sql-development-95/writing-a-simple-replace-query-335014.html

So, what if I wanted to do a search and replace for a single quotation? I can't use that in my search. How to get around that?

example:

Code:

update NamTable
set namstr=replace(namstr,''','')

View 1 Replies View Related

Query To Replace Part Of Field In PHPMyAdmin

Nov 27, 2011

I have a table called 'wp_postmeta' (Wordpress) which contains a column called 'meta_value'. A typical field in this column looks like this:

Code:
a:22:{s:12:"productimage";s:104:"http://www.disobeyclothing.com/wp-content/themes/eCommerce3/images/tshirts/SurveillanceSociety-Black.png";s:13:"productimage1";s:104:"http://www.disobeyclothing.com/wp-content/themes/eCommerce3/images/tshirts/SurveillanceSociety-White.png";s:13:"productimage2";s:0:"";s:13:"productimage3";s:0:"";s:13:"productimage4";s:0:"";
s:13:"productimage5";s:0:"";s:13:"productimage6";s:0:"";s:5:"pr

[code]...

I've tried to run the following query but each time I run it, it wipes all data in the field:

Code:
UPDATE wp_postmeta set meta_value=replace(meta_value, 'URL...')

I've also tried this query, but it has the same effect:

Code:
UPDATE wp_postmeta
SET meta_value = replace(LTRIM(RTRIM(meta_value)), URL...

View 5 Replies View Related

Replace Column Value With MAX() Query In Dataflow Before Insert

Jun 2, 2006

Hi,

We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.

Just to be clear:
column id (int)
column name (string)

Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table

Which transformation do I use to replace the value of the id column with this new id?

thanks,

Stephane

View 3 Replies View Related

SQL Query For Special Characters

Apr 18, 2007

On the bills that our system generates there is a comments field that users fillout. We have occasional problems with special characters in the text messing up the validation code. Does anyone know of a query that can identify special characters in a text field? Like carriage returns, tabs, etc.?
Thanks, Dave

View 1 Replies View Related

SQL Query Help - Accented Characters

Oct 10, 2007

Hello, I am trying to filter out any accented characters from a field in a select statement. I want to keep the accented version in the database, but have it convert when exporting.
Here is a sample of the php code that I used to use to accomplish this...

Code:

$shipfirstname = ereg_replace("[��]", "a", $shipfirstname);
$shipfirstname = ereg_replace("[��]", "c", $shipfirstname);
$shipfirstname = ereg_replace("[��]", "e", $shipfirstname);
$shipfirstname = ereg_replace("[��]", "i", $shipfirstname);
$shipfirstname = ereg_replace("[��]", "n", $shipfirstname);
$shipfirstname = ereg_replace("[��]", "o", $shipfirstname);
$shipfirstname = ereg_replace("[��]", "u", $shipfirstname);
$shipfirstname = ereg_replace("[�]", "y", $shipfirstname);

So far, I have this sql code, but I will have to use a number of nested REPLACE statements to accomplish the same thing.

Code:

(SELECT REPLACE(ShipFirstName, '�', 'a'))AS ShipFirstName,

Does anyone know an easier way to filter out these characters?

View 5 Replies View Related

MDX Query Above 8000 Characters

May 23, 2006

Hi,

I have a MDX query which is of an aprox length of 10000 characters. I
have to execute the query from within the stored procedure in sql. To
run this query I use the openrowset method.

If the length of my query is less than 8000 characters my query
executes perfectly, but the moment it exceeds 8000 characters it stop
working. Please suggest a solution for the same.

Sample Code:

declare @mdxqry varchar(8000)
declare @SearchCond varchar(8000)

set @SearchCond = @SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @TDate
+ '" '

set @mdxqry = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
'''''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @ToDate + '"), "Yes", "No")''''' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
' +
'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '

set @mdxqry = @mdxqry +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @SearchCond +
'))} on 2, ' +
@BranchFilter +
'FROM InterestAnalysis'''

set @mdxqry = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @mdxqry + ') as a'

exec(@mdxqry)

I have already tried splitting my query into smalled chunks and
executing it, but still I face the same problem.

This is how I have Done it:

declare @mdxqry1 varchar(8000)
declare @mdxqry2 varchar(8000)
declare @SearchCond varchar(8000)

set @SearchCond = @SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @TDate
+ '" '

set @mdxqry1 = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
'''''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @ToDate + '"), "Yes", "No")''''' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
'

set @mdxqry2 = 'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '

set @mdxqry2 = @mdxqry2 +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @SearchCond +
'))} on 2, ' +
@BranchFilter +
'FROM InterestAnalysis'''

set @mdxqry2 = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @mdxqry + ') as a'

exec(@mdxqry1 + @mdxqry2)

Thanks in Advance

Charu

View 5 Replies View Related