SQL 2012 :: Script To Remove Any Foreign Characters From Table
Sep 14, 2015
I have a table that is riddled with weird characters. So far I have found an escape character for PDF files and a trademark sign. These characters are crashing my SSIS packages. I am able to remove these characters with an update script...
Update TABLE
set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(65533) COLLATE Latin1_General_BIN2, '!');
Update TABLE
set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(1671) COLLATE Latin1_General_BIN2, '!');
This works fine, but my question is...
I would like to write a script that removes all foreign characters with the exception of the normal characters like (@,#,$,%,etc). I need a dynamic process that handles this so I am not losing time sifting through over 20,000 rows of data and changing my update script to remove a specific column. Although this method works, I would prefer a dynamic query. I intend to wrap this in a stored procedure that loops through all columns in a table (as parameter).
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'
Hi I have a table with a few hundred emailadresses. How can I delete all quotes (') from the addresses, so that 'email@email.com' is replaced as email@email.com. Thank you zipfeli
i have few fields that contain foreign characters with diacritic marks which are not getting imported correctly.
below is the import format:
- File type: ASCII - Row delimiter: carriage return and line feed {CR/LF} - Column delimiter: Tab - Text qualifier: None
Please advice.
Here is the errors i'm getting:
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Country_str_local_long_name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "Country_str_local_long_name" (37)" failed because truncation occurred, and the truncation row disposition on "output column "Country_str_local_long_name" (37)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "L:Country.txt" on data row 6. (SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Country_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. (SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038. (SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. (SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039. (SQL Server Import and Export Wizard)
Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!
Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitiions:
CREATE TABLE [dbo].[Locker]( [lockerID] [int] IDENTITY(1,1) NOT NULL, [schoolID] [int] NOT NULL, [number] [varchar](10) NOT NULL, [lockID] [int] NULL CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
[code]....
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999 insert into test.dbo.Locker ( [schoolID], [number]) select distinct LKR.schoolID, A.lockerNumber FROM [InputTable] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID) order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables?
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitions:
CREATE TABLE [dbo].[Locker](  [lockerID] [int] IDENTITY(1,1) NOT NULL,  [schoolID] [int] NOT NULL,  [number] [varchar](10) NOT NULL,  [lockID] [int] NULL  CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
[Code] ....
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999 insert into test.dbo.Locker ( [schoolID], [number]) select distinct LKR.schoolID, A.lockerNumber  FROM [InputTable] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID) order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables? Thus can you either modify the sql that I just listed above and/or come up with some new sql that will show me how to accomplish my goal?
How do I remove the first 5 characters from a column?
I have a list of id's - 9999925173 9999924037 9999924063 9999924053 9999924053 0 0 9999924053 9999924049 9999924037 9999944659 9999924053 0 9999924032 9999924037 9999924053
For some reason, we add a 99999 to our ID's and in order to compare to the customers data, I need to remove the 99999 (if they exist) from the column. How would I do that? LEN?
Hi all, I have a stored procedure that generates the following SQL WHERE clause UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND It is good except that it i can not remove the last AND which is not neccessary at the end of the clause. I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .
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...
We have a package that does a simple table to table data loading. Both source and destination have the same Codepage (850) and collation (SQL_Latin1_General_CP850_CI_AI). However, there are some foreign language characters in the source table, and it gets messed up when it's loaded into the target.
Here's an example for a column with this type of text:
I have tried to load this row into another server in a different location and it works just fine, however the original target of data displays the text incorrectly.
We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table. Â This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.
For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables? Â While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.
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
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
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?
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.
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.
I have a table tbl_rules. This table will define rules for each role. I have not yet defined the fields for the rules. But the table definition is as below:
Create table tbl_Rules ( ID int identity(1,1) not null, Role_ID int not null, primary key (ID), constraint fk_RoleName foreign key (Role_ID) references tbl_Role(ID) )
The table tbl_role has two columns as below:
ID RoleName 1 Manager 2 Analyst 3 Admin
So far so good. I created the tbl_rules.
But what i want to do is when I do select * from tbl_Rules, i want to show as below:
ID Role_ID 1 Manager 2 Admin
Instead it shows:
ID Role_ID 1 1 2 3
Is there a way to do this? The goal is to return the select * from tbl_Rules results to a gridview to enable adds and changes. I could do this by doing queries for each column, but I was hoping to make it easier. Not sure if this is even possible.
I created a mirrored DB, added a new datafile to the principal using a path the mirror can't access. As a result the mirroring session was suspended because the mirror stated: "CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file".
Fine, so I disabled mirroring on the principal (SET PARTNER OFF) which worked just fine, but the mirror stayed in a mirrored state, Object Explorer saying:"(Mirror, Disconnected / In Recovery). Sooo, I tried to disable db mirroring on the mirror using ALTER DATABASE db SET PARTNER OFF;, which completed successfully, but the DB STILL remained in a mirrored-configuration.
I tried ALTER DATABASE db SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS which resulted in
"Msg 1404, Level 16, State 4, Line 1
The command failed because the database mirror is busy. Reissue the command later."
I tried taking the DB offline or restoring it from a backup, but all these operations resulted in "The operation cannot be performed on database "db" because it is involved in a database mirroring session or an availability group."
The only solution I can think of at the moment is shutting down the instance and deleting the data and log-files of the mirrored db, which would be just fine because this is just a test installation, but it would be not quite as easy in a productive environment.
Is there any other way to remove the mirrored state from a disconnected mirror or to simply get rid of the db entirely to perform a recovery?
