SQL Server 2008 :: Normalizing Data Prior To Migration (Update String To Remove Special Characters)

Aug 21, 2015

I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.

I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.

There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.

Special Characters are defined as #%&*:<>?/{}|~ and ..

While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.

Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16

The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.

In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.

My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.

I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: How To Remove Special Characters From Column Value In A String

May 16, 2015

I want to remove special characters from a string in sql like <?> in a column value in a table.

View 1 Replies View Related

SQL Server 2008 :: String Break Into First Rows And Then Columns Based On Special Characters?

Jul 1, 2015

Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.

Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not

CREATE TABLE [dbo].[Equipment](
[EQU] [VARCHAR](50) NOT NULL,
[Notes] [TEXT] NULL,
[Facility] [VARCHAR](50) NULL)
INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility])
SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union
SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION
select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA'
SELECT * FROM dbo.Equipment

I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.

View 2 Replies View Related

SQL Server 2008 :: Use Cursor To Run Dynamic To Find Special Characters Used In Database

Apr 16, 2015

I need to find all uses of special characters in a database. I used the following code to do this:

USE dbName
GO
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
GO

[code]...

This will check all tables in the database, but if you want to check specific tables you can uncomment the line in the where clause and specify tables to be checked. The query will return any text fields that have any characters other than letters, numbers or spaces.

This code works fine for me because all the tables in my database have single column primary keys. However I know how much Jeff Moden hates cursors or RBAR queries, so my question is could this have been done by any method other than using a cursor?

View 9 Replies View Related

How To Remove Non-Numeric Or Non-alphameric Characters From A String In Sql Server 2005

May 16, 2007

Hi to all,
I am having a string like  (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
 
 

View 2 Replies View Related

How To Remove Non-Numeric Or Non-alphameric Characters From A String In Sql Server 2005

May 16, 2007

Hi to all,
I am having a string like  (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
 
 

View 2 Replies View Related

Removing Special Characters From A Number String.

Jul 11, 2001

Hi,

I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.

Thanks, Mark

View 1 Replies View Related

SQL Server 2012 :: Select Case Statement To Remove Part Of String After One Or Two Specific Characters

Jun 3, 2015

I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'

Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport

How do I achieve this in a CASE Statement?

The following two case statements return the correct results, but I some how need to combine them into a single Statement?

,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'

,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'

View 8 Replies View Related

How To Implement T-SQL Update Command Using Special Characters

Mar 24, 2008

Hi!
I wanted to execute an update command in T-SQL using some special characters like single quote ('). when i try with this it fails to accept the command. For example the text which i wanted to update is something like this "<Choice index="3" value="Don't know"/>", here i have a single quote('), less/greater than signs (<>), double quotes and forward slash (/). when i try with this type of text it fails to update, the field data type is NChar. I need help on how to successfully execute with this type of data.

Need help.


Thanks In Advance

Devloper
Anil Kumar Dwivedi

View 8 Replies View Related

How To Remove Characters In String

Jun 20, 2008

There are unwanted characters(''','/','&'.. etc) in column.
I need to remove these characters

View 1 Replies View Related

SQL Server 2008 :: Remove Double Quote In String Fields

Feb 23, 2015

I have a SQL select syntax as below

0 AS SalaryMin,
2088 AS SalaryMax,
2088 AS BillableHours,
'Month' AS SalaryPaidCode,
0 AS SalaryBreakdownHourly,
0 AS SalaryBreakdownDaily,

[Code] ...

While outputting to CSV.file

I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1

How can I remove all double quotes in the string fields? so that O can get the result as below while the output
0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1

View 7 Replies View Related

Data Export - How To Strip Out The Special Characters

Nov 10, 2005

Hey everyone!
I'm doing an export from SQL into excel spreadsheet and then am going to clean out certain parts of the data with global search/replace. The problem is that the SQL data is full of special characters such as |'s and the little box looking characters.
How do I export without these characters?
I know its possible, I did it about 2 years ago and remember I did some crazy file conversion (make wk3 or something) but I no longer remember
Any help would be much appreciated!
Thanks,
Geoff

PS, attached is a screenshot of the data to give you an idea of what I'd like to strip!

View 1 Replies View Related

Removing Special Characters From Existing Data

Jun 1, 2006

I have a table with several columns of information that I wish to set up some form of schedule to go through this data and remove any special characters that may interfere with other code processes.

Mainly the coma's and the apostrophes. It really messes with my asp pages and scripts when retrieving this information and trying to do other things with it, so I need to figure out how to remove these from the tables so it does not cause these issues.

Knowing this, I cannot figure out how to keep the data in the row/column and just extract the special characters from that data. The other problem is, everything I try requires me to insert either a coma or apostrophe as part of the code string which in lies my issue.

How can I parse through my data, leave the data as-is, but just get rid of coma's, apostrophes, and double quotes?

Does anyone have a basic example that I can use to expand on?

View 1 Replies View Related

DB Engine :: How To Avoid Special Characters While Migrating Data

Jun 23, 2015

I have a source sql 2005 with the database collation SQL_Latin1_General_CP1_CI_AS and destination with sql 2012 with the same collation.

But the SQL server llvel collation is different, sql 2005 uses Latin1_General_CI_AI and sql 2012 uses "SQL_Latin1_General_CP1_CI_AS"

Now when i load the data from 2005  for one table to sql 2012 i could see special characters in one column. And i dont see that in the source database. Is there a way to avoid that or is it something we need to manually fix.

View 7 Replies View Related

SQL Server 2008 :: Querying XML Data Type With UNICODE Characters

Oct 12, 2015

I am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).

Below is the sample script.

DECLARE @XMLVAR XML
SET @XMLVAR = '<?xml version="1.0"?>
<POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'

SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME
,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER
FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)

View 1 Replies View Related

SQL Server 2008 :: How To Remove / Uninstall Management Data Warehouse

Aug 12, 2010

I have tried to set up the Management Data Warehouse on one of our production servers, but it is not working (not collecting any data) so I want to completely remove it and try the installation again. There does not seem to be any remove/uninstall option.

How to completely remove the Management Data Warehouse?

Version:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

View 7 Replies View Related

SQL Server 2014 :: BCP And Bulk Insert Of Special Characters

Jul 22, 2015

I'm testing, with SQL 2014 on the same DB, a procedure that extracts data from a table into a file and Loads data from that file into a different table which has the same columns as the initial table (I use a function to create the create table statement from the source table and change the name of the destination table)

when doing my bcp -c the record with the special character "é" doesn't make it to the file.
when doing a bcp -w the record with the special character makes it to the file but the bulk insert omits the whole record.

The result file in the case that makes it to the file is unicode. I'm not using a format file (don't see the need for it)

The bulk insert into the destination table that contains identical columns as the source (a mixture on int, varchar, char) --> didn't work

I also tried by building the columns of the destination table with nvarchars -- Still doesn't work.

I tried the bulk insert with:

codepage = 'ACP, 'RAW' --> didn't change anything. still didn't work.

It's a complicated process that takes 1 XML record that contains information + the Create Table Statement (to eventually be able to this on a different server/DB) + the title Row for each column + the Data... Each of these are created with a BCP command (all with the same options). they are then appended to each other with a copy /B c:file1.txt + c:File2.txt + c:File3.txt + c:File4.csv c:ResultFile

Once the result file is created I bulk insert the 2 first rows in one table "TableA"
create the tmp table "TABLE B" with the create table statement that is in "TableA"
and do another bulk insert of the remainder of the file into the newly created table.

What else can I try? Should I be creating a format file? what are the benefits of a format file?

It's a very long procedure that does both Extract and Load (with 12 parameters) not sure what I should put here.

View 6 Replies View Related

How To Remove {CR}{LF} And Tab {t} Characters From Source Data?

Nov 7, 2006

I have built a SSIS package that reads in data from a SQL Server 2005 source database into a flat file destination. The Row Delimiter is {CR}{LF}. The Column Delimiter is Tab {t}.

The data being read from the SQL Server database contains both {CR}{LF} and Tab {t} characters in various fields on several rows.

How can I process the input data from the SQL Server to remove these characters before passing it to the destination output file?

Sorry if this is obvious to all, but I am only just starting with SSIS...

Many thanks

Adrian

View 1 Replies View Related

How Can U Remove My Post ? Data Migration From AS400 To Sql2005

Apr 17, 2007

Hi,

Yesterday I had posted a query of How to transfer the Data from AS400 files to Sql 2005 tables with DTS and the error I received for the same. I am not able to view the replies since it has been deleted. Pls repost the same. I posted the query expecting for a solution but instead my question was deleted..... the very purpose of forum is defeated.



Regds,

Anu

View 4 Replies View Related

Problems Character Sets / Special Characters Dot.Net &<-&> Mssql-server

Feb 11, 2008

Hi,I come from the "dark side" php/mysql and there often problems withcharacter sets (utf-8, latin...) and storing data in datebase.Exists in the world of dot.net and ms-sql-server similiar problems?To precise: I have to store xml-data in database. Maybe its better toencode (like base64) the strings?Perhaps there are some links to read?Thanks.klaus.

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

SQL Server 2012 :: Function To Remove Excess Characters

Mar 5, 2014

I am looking for a function or way to return only results which does not include appended characters to order numbers.

For instance, below is a list of order numbers. I only want the order number that is SO-123456

OrderNumbers
SO-123456
SO-123456-01
SO-123456-2
SO-123457
SO-123457-1
SO-123457-02
SO-123458

I would like my query to only show the below results

SO-123456
SO-123457
SO-123458

What functions or query methods could achieve this?

I was hoping for something similar to RTRIM but that is only specific to white space.

View 9 Replies View Related

Special Characters

Jan 16, 2006

Hi All

I have a database I want to check whether any special characters are entered or not

E.g.

Post
-------

Director
Asst*Director
Ma^nager

I want to see all the entires where special characters like !@#$%^&*()_+ are entered

Plz help

Thanks

View 8 Replies View Related

Special Characters

Mar 10, 2008

Hi,

I an concatenating a string using char(187) as the separator. When I run the command "select char(187)" I get the symbol >>

However, when it writes to a csv file it is showing as an overscore instead.

Is there a known problem with special characters when writing to files?

View 2 Replies View Related

Special Characters

Apr 2, 2008

Our SQL 2005 databases have "SQL_Latin1_General_CP1_CI_AS"
collation settings on the server. One of tables has one column declared as "ntext".
When we try to insert "" character directly trough insert statement and retrieve it, we're getting right results. When we use web-services to insert data with this character (euro), and retrieving the data back, we're not getting this sign back. We are getting some characters like little squares. If I try to copy and paste them into notepad, I am getting ???
However, when I tried to place them here, I got them right:

š Ž ž Œ œ Ÿ

How can we assure visually they are inserted properly?

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

BCP Issue-Special Characters

Jul 6, 2000

Iam working on sql server 7.0.

I have to bring in data from a text file into the sql server database. I use the BCP utility in pulling the data.

The flat file data contains special characters like . This is the registered trade mark symbol. When I bcp the file using the character mode the special characters are automatically changed to . So If I am going to pull a data like ACRYLON, after the BCP I get it as ACRYLON.

Inorder to replace all these I have written a program but since it has to scan every record the time taken is more. Will in be possible to accomodate iun the BCP to pull the data with special characters.

If you have an answer to this please reply soon.

Thanks,

-Rajesh

View 2 Replies View Related

Special Characters - Search In DB

Mar 6, 2001

Hi !

I have an DB in SQL Server 7, and in Portuguese we have special characters like "","",etc. And I want to let the visitor to a site to do a search (written in ASP), and not to need to write the correct way (without the accents). But either he writes or not in the correct way, the results are the same, not necessarly in the same order. Is there a SQL Server mechanism that permits this functionality, without doing a very complicated SELECT (takes a lot of time) or replicating a field in the DB (takes a lot of extra space)...

Thanks !
Filipe Lopes, Lisbon, Portugal.

View 2 Replies View Related

German Special Characters

Apr 13, 2003

I have a problem inserting German special chracters into a MSSql table.

works fine, but are reduced to "_". I'm using a html form and a php script to enter the data into the database. Any idea why this isn't working? Funny thing is, when I use a german version of the SQL Server it also works without a problem... but I need this to work on ANY mssql server. Any help would be very much appreciated.

Michael

View 6 Replies View Related

Can't Insert Special Characters

Jan 2, 2007

Hi, I'm new here. Happy new year!

Simple question. I can not insert special characters such as ' " ( ) etc. What special escape I need to put in my sql statement?

View 2 Replies View Related

Special Characters In MSSQL.

Mar 7, 2006

Hi,I have a MSSQL Server communicating with an Oracle database through aMSSQL linked server using a MS ODBC connection.If I query the Oracle database through the Oracle ODBC 32Bit Test, theresult is fine:select addrsurname from address where addrnr = 6666;HKANSSONIf I do the same query within the SQL Query Analyzer (using the linkedserver), I get:select * from openquery(TESTSW, 'select addrsurname from address whereaddrnr = 6666');H?KANSSONI have tried to both check and uncheck the Automatic ANSI to OEMconversion, but the result remains the same.Does anyone know what to do to make the result display the specialcharacters in SQL Query Analyzer?Thanks,Kenneth

View 4 Replies View Related

Treatment Of Special Characters

Jul 20, 2005

Hi,This is a generic question, but for arguement's sake, let's say, myenvironment is SQL Server 2000.It seems that setting quoted_identifier off is the best way toaccomodate all sort of data input/update especially for data set thatcontains special characters like single quote as in O'Brien, otherfunky stuff like %^$*@#(!).However, this option won't help with a situation like data value of5'9",and let's say, we really can't predicate if this type of value will beused for ColA or ColB or ColC ...Examplecreate table #tmp (col1 varchar(30));set quoted_identifier off;insert into #tmp values("O'Brien's barking dog'");-- successinsert into #tmp values("O'book has funky (%^$*7) stuff");-- successinsert into #tmp values("John said "funky is OK" in his speech");-- failedGlobaly search and replace the double quotes before insert/update isjust too "expensive", not a good option.Now, if we can set a custom quoted_identifier, we can solve the aboveproblem easily, for instance,if MS SQL Server permits/acceptsset quoted_identier = '//$$';theninsert into #tmp values(//$$John said "funky is OK" in hisspeech//$$);(or the like)would succeedAny other thoughts/ideas? Thanks.Quote "Never stop thinking even though at times it may produce waste,which we all do, btw :) "

View 4 Replies View Related

Special Characters In TextBox

Jun 14, 2007

Currently I have got a report where I want to insert special characters (like @, !, ?) at the end of a String which will be displayed in a textbox of the report. Wenn I insert the String value "test!?" the report will return this value "!?test".



Does somebody know what is going wrong?

View 1 Replies View Related







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