SQL 2012 :: Ignore ANSI Characters In Data Comparison?
Aug 18, 2014
I am comparing two fields one from our legacy table and one in our new table structure that should have identical text data. The new field has an assortment of ANSI characters where the legacy data did not have these. Is there anything I can do that will ignore all ansi character differences? The only route I can think of is just do a replace on each ANSI type on the new column but there are quite a few character types.
I exporting a table of comments. There are some line returns in the comments. Some of these data are paragraphs of data! For some reason, when I am exporting the data, it treats the line return within the comment column as a new record. I am using a -c character data type so (newline character) is the row terminator. How do I get the BCP OUT to ignore a newline character within a record?
For example: ID~Comment -- ------- 1~This is a comment 2~Hi,how are (user hit carriage) you (you is part of next row in bcp out) 3~Next record
What happens when you add the Ignore Case flag into the mix?
I'm having a hell of a time - I'm dealing with an SCD situation using TableDifference component and I have both existing dimensions and new data coming in, each go through identical Case-Insensitive/Sort with remove duplicates, but I'm getting identical new and deleted records detected - I think because of ordering issues. I'm still trying to whittle the test case down, but I think data from all around the records I'm investigating seems to get sorted in between them, so I'm having trouble getting a small test case built.
I think the mixed case data is the root of the problem, and I think the design is bad, but before I go back to the technical lead, I need to understand enough to show that you cannot take two pipelines sorted and de-duped case-insensitively and then do a case-sensitive table difference operation.
I need to pull address info and need to match customername from a spreadsheet. Due to bad design, in our database instead of having a 'status' for the customer, they just added ' - ACCOUNT CANCELLED' to the customername
I'm currently working on a series of comparison queries, between data from different countries.
The bad thing about this is, that many countries use special symbols such as ^ ~ ¨ ´ ` which basically means I have a tough time making the query effecient.
How would I work about converting Jiñi to Jini, so I can compare properly. Is there any way at all?
A question for everyone: With the introduction of SQL 2005, we now have to use ANSI-92 T-SQL Syntax and I was wondering if anyone had written a tool to convert queries from old ANSI SQL to the new syntax.
We have some code that has to change for the outer joins, but we also have a lot of code that should change for the inner joins. It doesn't seem that difficult to write something that parses an old piece of code and at least suggests a new version. Especially if the conversion code wasn't SQL code.
I've been using this syntax for years on SQL Server and now comes the time to convert to SQL 2005 (90 compatibility). This syntax returns four rows. Basically it returns one row for each servername/component/context/property/value even when there does not exist a property of 'fff' since it's a left join:
Code Block select t1.* from tblconfiguration t1 ,tblconfiguration t2 where t1.component = 'AdjProcessUtility' and t1.servername *= t2.servername and t1.component *= t2.component and t1.context *= t2.context and t1.property = 'proc' and t2.property = 'fff'
When the converted (using SQL enterprise Mgr) runs it returns no rows:
Code Block SELECT t1.* FROM dbo.tblConfiguration t1 LEFT OUTER JOIN dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context WHERE (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff')
I don't really see how to change this query to make it work. I've searched the web and I really don't see any examples of left joins which use more than one column.
Here's the table definition:
Code Block CREATE TABLE dbo.tblConfiguration ( ServerName VARCHAR(30) NOT NULL, Component VARCHAR(255) NOT NULL, Context VARCHAR(255) NOT NULL, Property VARCHAR(255) NOT NULL, CONSTRAINT PK_tblConfiguration PRIMARY KEY NONCLUSTERED( ServerName, Component, Context, Property ), Value VARCHAR(255) NOT NULL )
I use this table to define reports and there attribues. The rows repeat themselves except for the Property and Value columns Here is some of the data:
Hi everyone.. can anyone help me on how to solve my problem regarding on Select.. im using PB6.5 and running on MSSLQ2005 database.. i attached an image for your reference.. thnks!
My intention is to make a copy of a database with some data excluded. One way to do this:
- Make a copy-only backup of the source database - Restore the backup as a new database - Delete undesired data from the new database
In my case, the source database has two file groups, primary and secondary. The secondary has just one table with huge amount of data and this file group appears as one physical file. Is there any way to make a partial backup, which does not include this file group or table, and it would still be possible to restore the backup to a new database? My point is to avoid copying huge amount of data if it won't be needed.
They both drop and use a static table, this will be changed for production to use a temp table. there is a slight difference in how the create the table and when it comes to executing them if the other has been run first it errors because of the column definition or names.
Now as I said I will change these to use a temp table rather than a static staging table for production but as one of the first parts of my proc uses the IF OBJECT_ID ('dbo.tab', 'U') IS NOT NULL DROP TABLE dbo.Tab;
Then I would expect the table difference to not matter as the table is being dropped...
Is there a limit on the size of the strings on both sides of the '=' sign for string comparison? If I have two varchar(max) strings, will the comparison be done beyond 8,000 characters?
What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account], sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy], [DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default:
SQL_Latin1_General_CP1_CI_AS):
USE AdventureWorks2012 ; GO
--Returns 5 records SELECT pp.Name FROM Production.Product AS pp WHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ; GO
The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".
Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.
USE AdventureWorks2012 ; GO
--Returns 8 records SELECT pp.Name FROM Production.Product AS pp WHERE pp.Name >= N'Short' AND pp.Name <= N'Sporu' ; GO
What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2)) Declare @S nvarchar(255) Insert into @T Values ('AR'), ('AT'), ('AW') Set @S = 'Auto Repairs' Select * from @T T where case @S when 'Auto Repairs' then 'AR' when 'Auto Target' then 'AT' when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take:
1. script the source table's schema to a create 2. the new script:
If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U') CREATE TABLE [dbo].[EventlogHistory]( [LogID] [int] IDENTITY(1,1) NOT NULL, [ProjectID] [int] NOT NULL, [Description] [nvarchar](max) NOT NULL, [EventType] [varchar](10) NOT NULL, [IP] [varchar](50) NOT NULL, [UserLogon] [varchar](30) NOT NULL, [CreatedOn] [datetime] NOT NULL, ArchivedOn datetime default getdate()) insert into EventlogHistory
It throws me the error message saying "String or binary data would be truncated" which is nonsense to me, I need to let sql ignore the error, it is ridiculous to do a max(len()) to find out as it should never happen, right?
I have an Employee table with 3000 records and an Excel file having themodified data of those emplyoees. Some of the data of Excel may be sameas that of table data but some may differ. EmpId is the unique field.Other than this field, other fields of Excel may have modified data.Ineed to compare the data from SQL Server table with Excel Data.I decided to write a VB Program having two recordsets,one for SQLServer and other for Excel and compare each field's value. If themodified value is found then update that to table. Is there any way tocompare in SQL Server itself?Madhivanan
Data flow A take data from the Excel File A, Data B from Excel File B, Data C from Excel File C. What I'd like to do is that if something goes wrong on Data Flow A I would be alerted but the package should continue to running. The same for the DataFlow B, if A it's ok go on, if B fail send me the mail but continue until the end (so running the Data Flow C).
Hello All, I have two tables T1 and T2 with the same data structure. I need to compare T1 with T2 for all columns and update T2 for deleted, inserted and updated rows. How can I do this?
Short version: How would you accomplish comparing ALL the values in two tables wherein the PKs for each table are identical?
Long version: SQL Server 2000 database with two tables of identical structure having rows with some new data, some old data.* It's easy to determine which rows are 100% new by looking for PKs not in the old data. For the remaining rows, I need to make certain no rows have been modified. * Actually, there are hundreds of pairs of tables as described. The data comes from flat files, and I'd rather use the database than Perl in this case.
I can write code to generate the list of columns for each table and then query these as sets. Is there any other method that might work better?
I am not sure if this is the correct forum for my question but I will give it a shot. I want to be able to create a dataset from an ODBC datasource and compare it to existing data in my SQL DB. If there are changes (inserts/updates/deletes) I want to raise seperate events for each row state. Is this possible to do with SSIS/DTS or am I on the wrong track with what I am trying to do?
Can any one give a summary of difference between Data Stage and SSIS . we are in process of Migrating existing Jobs in Data Stage to SSIS .so i am prepareing a comparitive report between Data Stage and SSIS . help us pls
We have identity functionality for integer , Similarly i want for characters .i.e.
EX: ID Name Sal CD101 A 1000 CD102 B 2000 CD103 C 3000 CD104 D 4000 CD105 E 5000
I want output like above, I wont provide Value for ID column. It it should take automatically while inserting data into table.(like identity column)? Is it possible in SQL Server?
I've been testing a variety of database schema migration tools. Ourcompany purchased Embarcadero Change Manager a while ago but we'vebeen less than satisfied with the results.We are looking for a tool that can compare a source/developer databasewith a target/client database and then make the necessary changes.The tool will need to update stored procedures, tables, indexes,constraints, etc. The tool will also need to make the changes in thecorrect order based on dependencies and relationships.Anyone have any recommendations?
I have a requirement to compare data between two tables in SQL Server.
What is the fastest way to do it using SSIS? There are approx 6~7 millions of records in each table.
My solution: Read both the tables and store the data in Object Type variable. Then run an except query. But I am stuck at except query part. How do I implement it?
I need extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest] CREATE TABLE [dbo].[StoreNumberTest]( [StoreNumber] [varchar](50) NULL, [StoreNumberParsed] [varchar](50) NULL) INSERT INTO [dbo].[StoreNumberTest]
[Code] ....
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117 CA132 OH174 MD163 FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.
