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


Replacing Characters In A Text Field

Jul 20, 2005

I have a large table, tblMessage, which stores e-mail messages in textfields. I need to remove the carriage returns the data in these fields,but I have not yet figured out how to do so.I thought that the way to do this would be with the REPLACE function;unfortunately, of course, the REPLACE function cannot work with TEXTfields. I tried CASTing the text field to VARCHAR(8000); however, someof the rows have more than 8000 characters in the text field, so it bombs.Here is the SQL that I tried:selectmsgID,msgSent,msgFromType,msgFromID,msgSubject,REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') ASnewMessage,msgOriginal,attIDinto tblMessageNewfrom tblMessageI'm at my wit's end. Truncating the text field to 8000 character is anacceptable option, but I can't even seem to be able to do that.I'm using SQL Server version 7.

View 2 Replies View Related

Replacing Characters In SQL

Aug 13, 2004

I am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash I know to replace it with a double backslash \. But what if it is a " double quote what do I need to do to get it to appear as is?


View 2 Replies View Related

Replacing Illegal Characters In Table

Dec 4, 2006

i'm trying to figure out a way to replace a handful of "Illegal" characters in our SQL tables. We are using Project Server and some of these characters are causing errors or other issues in OLAP Cube.

I'm trying to figure out a way to change the following characters to an underscore ( _ ) :

illegal characters are: / ( ) . , ' : - &

Can I just create a SQL query that loops through a column to replace all of these characters at once? or do I have to replace one character at a time?! I tested replacing one at a time and it works with the REPLACE function.

I'm not all that familiar with MSSQL, I've spent past few years working with MySQL instead.

how can my query loop? or would using CASE help me out?

thanks. any feedback is much appreciated. If you need to know more, let me know.


View 2 Replies View Related

Urgent-Replacing A List Of Characters From A Column

Nov 13, 2001

this is my second attempt to get an answer to this question.
We want to strip our firstName column and lastname column of any punctuation that might be present.
What's the best of doing that?
Is my only choice to write a nested REPLACE for each character we want replaced (which will end up being very very long) or is there another way.

View 2 Replies View Related

Replacing NULL Characters From Within A Flat File With Script?

Jul 25, 2007

Here's one for the group:

I have a fixed-width flat file. Within some of the rows, I have embedded NULL characters. The inherit problem is that NULL characters are string terminators, so using a flat file source doesn't allow the capturing of these NULL characters or any characters after the first NULL character -- only the string up to the NULL character.

So, within SSIS, what would be the best way to replace NULL characters with a SPACE character? My file is fixed-width, and replacing with a space will allow me to keep the length the same. I am not opposed to running a script task against the file first (before using my flat file source), but would need some guidance as I'm not a .Net guru, by any means.

Unfortunately, going to the bank to have them correct this file has proved fruitless. We're going to have to deal with these characters on our side.


View 7 Replies View Related

SQL Server 2012 :: Replacing Recurring Characters In A String With Single Character

Jan 20, 2014

I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.

For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:

SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');

and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.

View 9 Replies View Related

How Can I Store Over 16000 Characters To Sql Table Field With Language Specific Characters?

Feb 19, 2008

In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?

View 3 Replies View Related

Removing Invalid Characters

May 6, 2008

here is my data

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

Desired data

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?


20116267 { E

I'd like to get only

I'd appreciate your inputs!


View 6 Replies View Related

Replacing Field Values

Oct 9, 2007

I am trying to replace field values in SSIS. For exapmle, I have 'unkown' and 'N/A', and a few other values that mean the same thing, and I want to give all these fields the same value. I have not been able to find a tool in the toolbox to make this type of change. Is there one? And, if there is, do you know a link to a page explaining its use?

The closest I have come is the Derived column String Transformation:Replace. But, I have not been able to get this to do what I want. If you know of a good reference explaining this tool's use I'd appreciate it.

thanks much,


View 4 Replies View Related

Transact SQL :: Replacing Names With Stars

Oct 30, 2015

I'm struggeling a bit with this one, goal is to show the first 3 characters of the name and replace the remaining characters with *** stars. Also the number of stars needs to match the lenght of the name as in the example above.I've tried:


View 5 Replies View Related

Transact SQL :: Replacing Isnull With Previous Value

May 25, 2015

I am having table like below,

Period Pointvaluemonth area
201109 100 10Septembertn

[Code] ....

Then i want to copy september value for october and november and Jan value for feb & mar like next null values should get replaced with previous not null value ...

My desired output is ,

Expected Output is…..

Period Pointvaluemonth area
201109 100 10September tn

[Code] ....

View 7 Replies View Related

Prevent Invalid Characters From Being Entered

Sep 24, 2006


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?


View 3 Replies View Related

Replace Invalid XML Characters Using SQL Query

Apr 21, 2008

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.


View 14 Replies View Related

Removing Or Replacing Part Of A String In A Field?

Mar 4, 2008

Hi all I was wondering whether it was possible to remove or delete part of a String in a Field? Lets take for example I have:

- A Table called: Table_1
- A Field called: MyField
- MyField contains the value: Hello I am on the msdn forums

Is it possible to perform an UPDATE Query which deletes part of that sentence? If this was hard coded it would be rather simple but if the phrase was changing which would lead to the sentence also changing is it possible to do this?

Lets say I wanted to remove the part which said msdn forums. Then UPDATE the field again which should leave out the bit msdn forums.

Appreciate the responses, Onam.

View 5 Replies View Related

Convert Field From VarChar To Int With Speical Characters In Field

Aug 29, 2007


I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.

How can I remove all speical characters and then convert that field to an int?

Also I tried the following query which did not work as well, same error about conversion.

UPDATE myTable SET field = CAST(field AS int)

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

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

Transact SQL :: Get A Set Of Characters?

Jun 29, 2015

aaa 12.35 bbb ccc 11.24 ddd STANDARD aa bb cc 100 dd xxxxx yyyyy.

I want to extract the value 100 from this using charindex, substring and related functions. The value 100 (or 200, or 300) occurs after 4 blank spaces after STANDARD.

I want get the value in lying in between the 4th and 5th blank space after STANDARD. This would give me the value 100 as output. But to implement it, I am not able to write the functions!

View 11 Replies View Related

How Many Characters Has My Field?

Feb 3, 2005

Does anyone know a function in SQL or how I can get the amount of characters of a field?

I have a column named NU_IPS wich contains data varchar type, that has a % symbol at the end, like 9.7% and so on... But in original table it can't be like this (it has to bem float type), I just want the number content, like this 9.7 For that I need in DTS put a query that convert it. That's why I need a function or something that can get the quantity of characters of each field.

So, It would be someting like this...

select substring(convert(varchar(getSizeField() - 1), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

It would cut always the last caracter, wich is '%'...

Any clues?
All posts are welcome, thanks.

View 5 Replies View Related

Transact SQL :: Getting Text Between 2 Characters

Jun 18, 2015

I have a column that is populated similar to below

[URL] ....

I need to extract just the 12345 portion. This will always appear after the first "=" and always be proceeded by &UL. I know how I can do this separately, which would be like

To get 12345&UL

replace(cast(MyCol as nvarchar(max)),
replicate(cast(' ' as nvarchar(max)),10000)),
10001, 10000)))

And then I could run an update on the table after doing the above step using something like


What I'd like to do is to have everything performed in one step, the above 2 SQL statements combined as one statement, so a separate update does not need to be ran.

View 7 Replies View Related

Updating 3 Characters In A Field

Mar 5, 2002

I have a sql database that includes a table of customer contact information. The area code for many of my customers is about to change. Is there a way to mass update the phone number field so that all phone numbers that currently start with 111 change to 222 ? Ex 1115554444 to 2225554444 ?

Thanks in advance.

View 2 Replies View Related

Sp To Extract Characters From One Field To Another

Aug 15, 2007


I have a table in sql server that has a field with numeric characters, i would like to select some but not all characters from this field and insert it into a new field. The field that i want to get some characters from has other characters that i dont need, It has a lot of zero's next to the numbers that i need. so far i created a stored procedure, but its not working. I want to extract the characters and insert them into a new field in the format "000.00" or "00.00". The values are either hunderths or tenths, which makes it difficult for me to decide which characters to ignore. Please see my SP created so far below:

CREATE PROCEDURE dbo.spUpdatePaymentDetails
AS UPDATE dbo.PaymentDetails
SET NewAmount = SUBSTRING('00' + TransactionAmount, 1, LEN('00' +TransactionAmount) - 2) + '.' + SUBSTRING('00' + TransactionAmount, LEN('00' + TransactionAmount) - 1, 2)

the character "." is not in the original field so therefore i would like to insert it into the new field.

Please help urgently.


View 2 Replies View Related

Transact SQL :: NVARCHAR Field Getting Truncated After Increasing Field Length?

Aug 28, 2015

I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.

It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:

Inserted Value -> Result in table

123456-8 -> 123456-8      *Correct
1234567-9 -> 1234567-     *Wrong
123456789 -> 123456789    *Correct
1234567#9 -> 1234567#     *Wrong
1234567a9 -> 1234567a9    *Correct

Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?

I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.

View 3 Replies View Related

Transact SQL :: How To Get All Characters Before Special Character

Jul 21, 2015

I would like to know how can i only get the characters before the special character?

For example if the mail id is i need to extract The.Champ123 and if the mail id is I need to get TheChamp.So basically i would like to get the characters from the string before '@' character.

View 14 Replies View Related

Transact SQL :: How To Query International Characters

Aug 14, 2015

i've worked with SQL Databases for years now, but never needed to deal with international characters.  I am trying to search strings like surname in a column that might have international letters/characters, such as:

select * from [dbo].[User] where LastName like '%Hekimoğlu%'

It doesn't retrieves anything.  I've googled for while in a hope to find some solution quickly, but to my surprise I couldn't. how to query string columns that include international characters such as above.I am using SQL Server 2012, Nvarchar(100) for column.

View 5 Replies View Related

Transact SQL :: Pivot And Invalid Column Name

May 13, 2015

I'm trying to Pivot and I keep getting an "Invalid Column Name" error, which I can't figure out since, if I run the query and exclude the Pivot statement, the query runs fine.

ItemNmbr Char(31) not null
SetupTime_I Numeric(19,5) not null
WCID_I  Char(11) not null
select ItemNmbr,SetupTime_I, WCID_I from RT010130

Now run
select ItemNmbr,SetupTime_I, WCID_I
from RT010130
pivot (sum(SetupTime_I) for WCID_I in ([BLA01],[URE02])) PVT

And I get an Invalid Column Name error for both SetupTime_I and WCID_I - which, as far as I can tell, is demonstrably incorrect.

View 5 Replies View Related

Transact SQL :: Invalid Character Validation

Jun 3, 2015

if the variable contains any of the character  that is not present in the  following Below table then it should display "InValid Character" else "valid Character"

SET @MyString = 'ABC$['


View 3 Replies View Related

How To Strip A List Of Characters From A Field

Nov 8, 2001

what the best way of stripping out a list of characters from a specified field in a table. e.g If first name consists of ABCD'E-FSA, we wnat to strip the ' and the -. There is about 15-20 characters like that.
what's the best way of doing it other encapsulating in the replace function that many times.

View 2 Replies View Related

Query/select The First 10 Characters In A Field

Jul 25, 2007

I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B.

So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi'

I know I've done this before but can't find a good reference.

It's something like this when calling a query within a WHILE loop:

SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8)

Am I close? Any help would be appreciated.

View 3 Replies View Related

Unlimited Number Of Characters In A Field

Feb 4, 2005


I am developing a message board using ASP on IIS and SQL Server 2000, and I am running into a problem.

When a user enters their post, they enter their user name, subject, and of course all of their post content. Now the post content should be an unlimited number of characters since it could end up being multiple paragraphs.

I know I could just use an ASP file system object and write the text to a file, but I wanted to save all of these paragraphs as a field in a database table so I could pull it from the database so it could be edited by the user.

Is there a way to make a field be able to take in an unlimited amount of characters?

Or is this the wrong way to do things; is it bad to have an unlimited amount of characters in a table field? Should I stick to using the ASP System File Object?

View 1 Replies View Related

Removing Characters In Field Grouping

Jun 15, 2007


I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?

Code Snippet


View 2 Replies View Related

Copyrights 2005-15, All rights reserved