I'm working on a program that reads in zip codes and then does some checking on them. Occasionally, the end user typos or transposes digits. It's easy enough to identify bad zip codes, but I would like to offer up a single recommendation. The problem is that there are often multiples to choose from. Is there a function or has anyone tried to figure out the delta between two strings?
Thanks
Hi,
Assuming a table with a column defined char or varchar.
I have a SQL query like this :
Select * from table1 where column1='Building'
It returns the same result that
Select * from table1 where column1='BUILDING'
It is my understanding SQL Server (verison 7 or 2000, I tried on both) is lower/capital insensitive by default when it is installed. If I want SQL Server to be case-sensitive with my char or varchar columns, where can I set it?
Is it at database level or server level I can find this setting . What is the setting that control it?
Best Regards,
Alain Gagne, Lead DBA
gagnea@msagroup.com
i have 2 tables
table1
id name salary
2 sas 2000
3 jsh 2220
table2
id name
1 sas
2 jsh
how can we copmpare string like
name=name
Dear All:I encounter one problem when I want to implement my thought. My thoughtis that user want to search a record of someone but maybe user wouldtype wrong name or spell name wrong. I wish to compare the string whichuser inputed to the database column using "Socre Method". "ScoreMethod" has a variable "grade" to accumulate the score. I want toconvert the string to char array, and compare the char one by one. Ifthe string is more accurate , the grade is more high. At last, I choosethe most higher score record to show. How to do this thought with tsql?Could give me some tips or guide to learn? I will appreciate yourkindness, thanks.
View 6 Replies View RelatedOur dev team wants to introduce a complex key, which is made up of
EventDate datetime2(5) = ‘2015-10-22 10:19:59.12345’
ConsumerID bigint = 1234
SiteID tinyint = 15
EventDate will be converted to a bigint and then to a hex value
= 2015-10-22 10:19:59.12345 = 2015102210195912345 = 1BF714C8A0D4F699
ConsumerID will be converted to a hex value
= 1234 = 4D2
SiteID will be converted to a hex value
= 15 = F
The hex values will then be concatenated together and stored as a string (varchar). The application will handle the creation of the complex key
= ‘1BF714C8A0D4F699-4D2-F’
I am trying to argue against this approach and get them to store the values in their native form, in separate columns as a natural key.
To make matters worse, we need to use partitioning, where the partition boundary will be on a hexed datetime2(5) at weekly intervals. I was really hoping a proof of concept would show that hex string comparison could potentially put a row under a wrong partition, but so far, on an 8 million rowset, it is working fine.
For example, in the proof of concept, right partition boundary:
= '1BF398D53DFA1800'
(2014-12-15 00:00:00.00000)
contains rows
= '1BF3983242B9C000-1-2'
(2014-12-08 00:00:00.00000-1-2)
Through to
= '1BF398C373960580-2FAF3003-2'
(2014-12-14 23：59：00.00000-800010243-2）
The prior partition boundary is 1
= 'BF3983242B9C000'
(2014-12-08 00:00:00.00000)
How is this working, given that the string lengths (varchar) are different and the row value contains dashes and the partition boundaries are smaller in length without dashes? How varchar string comparison is working here?
I'm trying to come up with a formula that will calculate the number of lines where two conditions are true.First, SLA must be either breached or achieved.And the second condition must be that the "country" and SLO group must be the same (these two values are located in different tables. So far I have only accomplished the first....
=CALCULATE(DISTINCTCOUNT([ID]),Data![SLA Result]="Breached")
I have tried adding FIND, EXACT or USERELATIONSHIP to the formula to no avail.... I keep running into the same error."The value for 'SLO Group' cannot be determined. Either 'SLO Group' doesn't exist, or there is no current row for a column named 'SLO Group'."
Country
SLO Group
SLA Achieved
SLA Breached
[code]....
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.
View 9 Replies View Related
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I pass a parameter to it. But if I hard
code it, it gives me the right result.
The I check if the field value of 'Email' is exactly equal to the parameter '@Email'.
The field 'Email' is varchar, and CID is integer.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn
Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com'
return
Can anyone tell me the reason for it.
Thanking you in advance
Tomy
DECLARE @Teams AS TABLE(Team VARCHAR(3))
INSERT INTO @Teams
SELECT 'IND'
UNION
SELECT 'SA'
UNION
SELECT 'AUS'
select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
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.
Hi,
I am performing data migration from a legacy sql server (2000) to a new one (SQL 2005). I am using SSIS packages to perform the migration.
The source legacy sql server is a live machine (used by the customer). Therefore, during the data transfer there would certainly be new records inserted/updated/deleted on the source legacy sql server.
Now, after the migration i wish to migrate the delta to the new sql server. how can i do this ?
Does SSIS have any provision to take care of it ?
how can I capture delta in SQL Server 2005 to refresh base tables in a data warehouse?
View 8 Replies View RelatedHi ,
i'm new to this platform and am wondering if there is an easy way to identify ADDs changes and deletes between two tables - or whether i will have to do a ror for row comparison using the business key?
Any suggestions would be greatly appreciated..
thank you George Bassili
Suppose a database server and client are separated by a low bandwidthlink such as DSL, and the client repeatedly issues a query for, say, acurrent product list.Suppose the product list is large, but only a handful of entries havetypically changed between queries. It would be nice if only the changesfrom last query to current one could be sent, saving bandwidth.Is there any way to do this?Thanks,--"Always look on the bright side of life."To reply by email, replace no.spam with my last name.
View 3 Replies View RelatedWe are trying to configure Delta View in our senario and are confused at a couple of points:
1. What is the difference between modify and modify_attribute? How does modify_attribute works at attribute level? Is it only for multivalued attributes?
2. Rather than having an extra table for Delta. If we would use the prime table itself with an extra field acting as a flag (0/1) (0=Prime Record, 1=Delta Record) and generate the delta view according to the flag. Would it be fine or we should have a different delta table only?
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.
Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table
Currently through my stored procedure, I am pulling the data from my source systems in the way Daily Truncate and loading the data in to my database table. Currently my query looks as follows,
SELECT
a.col1,a.col2,b.col3,b.col4,c.col5
FROM
a
left outer join b ON a.col1 = b.col1
left outer join c ON b.col1 = c.col1.
Now, I want to change the above one to delta process(I have updateddate field in all the tables). The requirement is any of these 3 tables has updateddate = getdate() then i need to take "a.col1,a.col2,b.col3,b.col4,c.col5" columns and push to destination
table.
I think usually delta process means we will use to consider the key table, in that table if there is an updated record will take that record and join with the other required tables to pull the updated record. But these logic seems different.how to build the query for the above delta process? Since it is an urgent requirement need to implement asap.
I have a table with the next structure:
DECLARE @MaxCountHistogram TABLE
(
MaxId INT IDENTITY PRIMARY KEY NOT NULL,
PublicationId INT NOT NULL,
ProviderId INT NOT NULL,
DateLog DATETIME NOT NULL,
Amount FLOAT NOT NULL
)
INSERT INTO @MaxCountHistogram
VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)
I need to take for each Publication and Provider and getting the diferential between two different months, for example:
Period Delta Amount Provider PublicationId
20150530 10.2564 3 432
20150630 3 Result of (13.2564- 10.2564 ) 3 432
Hi all,
In an approach of building an ETL tool, we are into a situation wherein, a table has to be loaded on an incremental basis. The first run all the records apporx 100 lacs has to be loaded. From the next run, only the records that got updated since the last run of the package or newly added are to be pulled from the source Database. One idea we had was to have two OLE DB Source components, in one get those records that got updated or was added newly, since we have upddate cols in the DB getting them is fairly simple, in the next OLEDB source load all the records form the Destination, pass it onto a Merge Join then have a Conditional Split down the piple line, and handle the updates cum insert.
Now the question is, how slow the show is gonna be ? Will there be a case that the Source DB returns records pretty fast and Merge Join fails in anticipation of all the records from the destination ?
What might be the ideal way to go about my scenario.. Please advice...
Thanks in advance.
i would like to know the best practices to choose the columns which should be used for delta?.If, i consider Customer ID as part of delta.
View 1 Replies View Relatedfunction
/*Title:
Created By:
Create Date:
Notes:This function is used to concatenate the fields of a table except any identity and hashbyte column passed into the function. It works for temp tables are phyisical tables.
** NOTE: The temp table has be on the same SQL connection to work. If you use this in SSIS you will need to make your connection persistant.
The original concept came from [URL] .... and was modified.
Revisions:
*/
ALTER FUNCTION [dbo].[get_hash_fields] ( @p_table_name VARCHAR(128),
@p_schema_name VARCHAR(20),
@chksum_col_name varchar(255) )
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @sqlString as varchar(max)
[Code] ....
Dear All
I am new to the topic T-SQL.
I am trying to use T-SQL to merge the content of two tables (table1 and table2) into one table making sure there are no duplication.
I wonder if any body can let me have a simple code.
Best Regards
Saad
Hey guys what would be the easiest way to create a report of value changes for particular records from one day to the next..... ?
Any suggestions would be greatly appreciated....
thanks,
Jonathan
Hi there! can anyone help me out??? I need to compare a date from the database and the system date which will be coded in a store procedure in SQL... HELP!!!!!
View 5 Replies View RelatedI have a table with a field with a bit datatype. When I execute the stored procedure line if @bitvalue = 1 begin ...
and the value is passes as 0 the statements beneath the begin execute. What am I doing wrong?
Hi...
I have a problem comparing dates in SQL Server 7.
I want to find results where date1 = date2.
I am having problems becausing SQL is also trying to compare the time which
is included in the field value e.g. 03/04/00 12:01:32
So even if the dates are equal the time may be different so the results are not shown?
I've tried SUBSTRING, and CONVERT,CAST etc.. and still can't get it to work
Any ideas???
Thanks.
Is there a way to compare two similar tables? I'm more interested in finding out if the data content is exactly the same or not between the two tables.
Thanks for your help.
We are converting our project into new version. They have done lot of changes in new version including normalization/denormalization. I need to compare the old and new database.
Do you have any standard script or procedure like SQL Compare software?.
Let me know what are the possibilties we need to check.
Your help appreciated.
Thanks,
Ravi
Good day,
I am receiving garbage data from my other department as follows.
I want:
'12/05/03' for example.
I get:
'//'
'00/00/00'
And other garbage. All of which crashes the transaction because it cannot be converted to Datetime.
What I would like to do is something like this:
IF @v_date > '01/01/50'
BEGIN <-- statement will crash if bad data)
Something to trigger a Boolean (True/False) validation of the data.
I want to know if it is even a date before I run it through my block.
Any thoughts?
I don't want to add a new IF block for every bizarre thing they send me.
Thanks ahead of time.
PS - I guess I could just raise an exception and handle it in a block that way, but am still curious.
Hi friends,
How can I do, in one Trigger, to returns if two columns, one of the Inserted and other of the Deleted, are differents ? I used this example:
If ( select Name from inserted ) !=
( select Name from deleted )
Insert Into Log_System
( Key_Mame, User_Name, Date, Field, Value, Table_Name )
Select 'COD. PROD: ' + Convert( char(10), I.Cod_Prod ),
SUSER_NAME(), GetDate(),
'Name', Convert(Char(255), D.Name ),
Convert(Char(255), I.Name), 'U', 'PRODUCTS'
From Inserted I, Deleted D
But, if one of them is Null, then the comparison result returns FALSE.
Examples:
1 - If in the Table are keeped null and the new value is different of Null
the comparison result returns FALSE.
2 - If in the Table are keeped a value and the new value is null the
comparison result returns FALSE too.
Excuse me, my English is not currently.
Thanks !
Rodney.
Hello im trying to run a simple query where i compare something simple like BEARING to a field in the database using this query.
Code:
SELECT table1.STK_NO, table1.STK_NO2
FROM table1
WHERE (LEN(CLEANSED_REF)>2)
AND ('BEARING' LIKE '%'+table1.CLEANSED_REF+'%')
Inside the table i can see the CLEANSED_REF field and its value is "000BEARINGBRNG000". When i run the query i get no records returned.
But if i run
Code:
SELECT table1.STK_NO, table1.STK_NO2
FROM table1
WHERE (LEN(CLEANSED_REF)>2)
AND (table1.CLEANSED_REF LIKE '%BEARING%')
The query returns the row i wanted. Can anyone tell me why this wont work the first way, but works the second?
Morning!
Folks, i want some links where i could find healthy stuff regarding New Features and Compliance levels, performance comparisons, TPC tests etc about SQL 2000 and Yukon specially.
I've to submit a document regarding Top database features that shall be used for a Medical-Billing Software in plan.
Howdy!
I have two tables that I am needing to link by the tables date field. In one table the date field is defined as varchar(23). The time for this field is always zeros.
Example: '2005-12-27 00:00:00.000'
The other table is defined as datetime, and it does have the date and time in this field.
Example: 2005-12-27 08:00:35.000
The problem i am having is
2005-12-27 00:00:00.000 does not = 2005-12-27 08:00:35.000.
Because I will never have more than one record on the same date I would like to be able to only compare the date. Example 2005-12-27 = 2005-12-27
Since the fields are 2 different field types, this is giving me a problem. Could someone please help. I have tried everything I know to do.
What I really need is the a way to format the datetime fields date into a string such as '2005-12-27'.
Hopefully what i have written makes sense.
Thanks