Finding Difference In Strings

Hey everyone, I am hoping someone can help me with a command that I am struggling to find. What I want to do is run a T-SQL command where it will return only the difference between 2 strings.

For example: If i have "This is a test" and "This is a test which will be good", then i want the command to return "which will be good" I believed there had to be this sort of a command, but i could not find anything close to it. Any ideas on how i would accomplish this?

Quote:

Originally Posted by willwmagic

Hey everyone, I am hoping someone can help me with a command that I am struggling to find. What I want to do is run a T-SQL command where it will return only the difference between 2 strings.

For example: If i have "This is a test" and "This is a test which will be good", then i want the command to return "which will be good" I believed there had to be this sort of a command, but i could not find anything close to it. Any ideas on how i would accomplish this?

You may need a stored procedure to do this. CHARINDEX and SUBSTRING functions may be useful|||what do expect when the strings are

"This is a test" and "wow,This is a test" ?|||

Quote:

Originally Posted by debasisdas

what do expect when the strings are

"This is a test" and "wow,This is a test" ?

I am not so concerned with what is before the string match, but more so with what is after the matched string.

Find start and end date strings

Hello,

I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:

Customer StartDate EndDate
A 4/1/04 4/15/04
A 4/15/04 5/1/04
A 5/1/04 5/15/04
A 5/16/04 5/28/04
A 5/28/04 6/5/04
B 5/1/04 5/15/04
B 5/16/04 5/20/04

The results I am looking for would be as follows:

Customer A : Outstanding 4/1/04 - 5/15/04
Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string )
Customer B : OUtstanding 5/1/04 - 5/15/04
Customer B : Outstanding 5/16/04 - 5/20/04

I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??

Thanks in advance!!bump..any one have ideas??|||How about something like this?

Declare @.sCustomer as varchar(5)
Declare @.dtStartDate as datetime
Declare @.dtEndDate as Datetime
Declare @.sSaveCustomer as varchar(5)
Declare @.dtSaveStartDate as DateTime
Declare @.dtLinkDate as DateTime

DECLARE Test CURSOR FOR
SELECT * FROM Test ORDER BY Customer, StartDate, EndDate

CREATE table #tmp (Customer varchar(5), StartDate DateTime, EndDate DateTime)

OPEN TEST

Fetch next from Test into @.sCustomer, @.dtStartDate, @.dtEndDate

SET @.sSaveCustomer = @.sCustomer
SET @.dtSaveStartDate = @.dtStartDate
SET @.dtLinkDate = @.dtEndDate

if (@.@.FETCH_STATUS = 0)
BEGIN
Fetch next from Test into @.sCustomer, @.dtStartDate, @.dtEndDate
END

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
if ((@.sSaveCustomer = @.sCustomer) AND (@.dtStartDate = @.dtLinkDate))
BEGIN
SET @.dtLinkDate = @.dtEndDate
END
ELSE
BEGIN
INSERT INTO #tmp VALUES(@.sSaveCustomer, @.dtSaveStartDate, @.dtLinkDate)
SET @.sSaveCustomer = @.sCustomer
SET @.dtSaveStartDate = @.dtStartDate
SET @.dtLinkDate = @.dtEndDate
END

Fetch next from Test into @.sCustomer, @.dtStartDate, @.dtEndDate
END

INSERT INTO #tmp VALUES(@.sSaveCustomer, @.dtSaveStartDate, @.dtLinkDate)

select * from #tmp

Drop table #tmp

Close Test
Deallocate Test

Have some fun.|||A similar question was asked in this thread.

http://www.dbforums.com/t1005647.html|||Thanks!! Both options worked!!

Find similar strings in two tables

I have two tables in the same SQL database. Both have a similar numeric
field. One field has the same number as the other field but is prefixed
with various letters. I know how to use LIKE but won't know the partial
string I am looking for. I am trying to use LIKE '%' + Field A or
something that will do this.

Eg.

Table 1 Field A is 'A12345"
Table 2 Field B is '12345"

I want to find every record in Table 1 Field A that contains the exact
Field B data.

*** Sent via Developersdex http://www.developersdex.com ***Select FieldA from Table1 where
convert(numeric,right(FieldA,len(FieldA)-1)) in (Select FieldB from
Table2)

Madhivanan|||Hi Robert
It is much easier to help when given the right information.
Please post your ddl (i.e. your create table statement) so that people
can know the data exactly.
Also be more specifi: are records in table 1 always with one letter
forllowed by 5 digits? or can it be ABC12345DEF ? Just an example.

without this information the best I think I can do is:
select A from T1 where exists
(select B from T2 where A like '%'+B+'%')|||robert lassiter (rlassiter@.shaw.ca) writes:
> I have two tables in the same SQL database. Both have a similar numeric
> field. One field has the same number as the other field but is prefixed
> with various letters. I know how to use LIKE but won't know the partial
> string I am looking for. I am trying to use LIKE '%' + Field A or
> something that will do this.
> Eg.
> Table 1 Field A is 'A12345"
> Table 2 Field B is '12345"
> I want to find every record in Table 1 Field A that contains the exact
> Field B data.

Assuming the the numbers are always at the end, this could do:

SELECT * FROM tbl1 a
WHERE EXISTS (SELECT *
FROM tbl2 b
WHERE charindex(b.fieldb, a.fielda) =
len(a.fielda) - len(b.fieldb) + 1)

Note that this is untested.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp