Pattern Matching - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pattern Matching

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-04 : 20:13:40

Hi Guys,
This is tricky.

I have two tables to match column named "Mixed" and update table1 with another column with a message.
SQL query will clarify this maze:

UPDATE Table1
SET Status = 'FAIL'
FROM Table1
LEFT JOIN table2
ON table1.(ascii(right(MiXED1,10))) = table2.Mixed2
where Table1.col2 = 'A'

Now: mixed1 in table1 has XX******** (10 digits) with XX prefix
I want to remove that XX prefix and replace it by "00"
and then match it with Mixed2 of table2.

Sample data:
Mixed1 in Table1 : XX000ABC01
Mixed2 in Table2 : 00000ABC01

now those two values should match.

Thanks !!

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-05 : 02:19:58

Ben, there are a couple things missing in your data like the definition of col2, but I think this may achieve what you're trying to do (used table variable instead of create statement)

declare @table1 table ([status] varchar(5), mixed varchar(10), col2 char(1))
declare @table2 table (Mixed2 varchar(10))

insert into @table1 ([status], mixed, col2)
values (null, 'XX000ABC01', 'A');
insert into @table2 (mixed2)
values ('00000ABC01')

update @table1
set mixed=replace(mixed,'x','0')
where left(mixed,2)='xx' --//Just have the filter on this in case xx can appear somewhere else in the string

UPDATE @Table1
SET Status = 'FAIL'
FROM @Table1 a
LEFT JOIN @table2 b
ON a.mixed = b.Mixed2
where a.col2 = 'A'

select * from @table1

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-05 : 15:10:45

It is not pick values from table1 & Table2. Please Help !

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-05 : 15:28:50

stuff('XX000ABC01',1,2,'00')

Jim

Everyday I learn something that somebody else already knew

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-05 : 16:45:39

create table #table1 (id int,name varchar(50),[Status] varchar(50), MatchID varchar(50), Action varchar(50))
create table #table2 ([planID] varchar(50))

insert into #table1(id,name,[Status], MatchID, Action)
select id,name,[Status], MatchID, Action
from My_table1
where Name = 'P'
and Action = 'I'

insert into #table2(planID)
select planid
from My_table2

update #table1
set MatchID = replace( MatchID,'x','0')

UPDATE #table1
SET Status = 'FAIL'
FROM #table1 a
left JOIN #table2 b
ON a.MatchID = b.planID
WHERE a.MatchID IS NULL

It is flagging "Fail" to even the matching records.
Thanks

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-05 : 21:55:53

Can anyone correct the query ?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-07 : 13:34:14

Look up MERGE and use it instead of all this mess.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources