473,216 Members | 1,257 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

Replacement for my LIKE Clause

Dear all,

I need some help from all my Transact SQL Guru friends out there..

Here is the scenario in its most simplified form.. ..

I have two tables.. A(Lookup table) and B(Transaction Table)

TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...
TableB Fields
EmployeeID
EmployeeName.......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK".... etc)

I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...

Therefore, this is what I had wrote initially..

update B
set B.EmployeeLocationID =A.EmployeeLocationID
>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null

This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...

For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match.... etc....

Therefore this is the expected match for my examples based on
requirement..

"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..
Looking forward to hearing from someone ASAP.. Please help..

Best regards,

VM...

Dec 27 '06 #1
3 1968
Interesting. Maybe this will give you and angle to try.
UPDATE B
SET EmployeeLocationID =
(SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL

Roy Harvey
Beacon Falls, CT

On 27 Dec 2006 15:24:44 -0800, va***********@wipro.com wrote:
>Dear all,

I need some help from all my Transact SQL Guru friends out there..

Here is the scenario in its most simplified form.. ..

I have two tables.. A(Lookup table) and B(Transaction Table)

TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...
TableB Fields
EmployeeID
EmployeeName.......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK".... etc)

I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...

Therefore, this is what I had wrote initially..

update B
set B.EmployeeLocationID =A.EmployeeLocationID
>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null

This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...

For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match.... etc....

Therefore this is the expected match for my examples based on
requirement..

"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..
Looking forward to hearing from someone ASAP.. Please help..

Best regards,

VM...
Dec 27 '06 #2
Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.

SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?

>A(Lookup table) and B(Transaction Table) <<
Why did you avoid clear names?

CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??

-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.

Can two prefixes belong to the same SAN? No specs given.

Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?

CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);

The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>I hope you get where I am leading this to, from my examples.. <<
No. Clear specs would have been nice, along with real DDL.

Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:

INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@my_foobar_trans_nbr, @my_emp_id,

(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),

Etc.);

You will need error handling code for prefixes that do not match.

Dec 28 '06 #3
Roy,

Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..

Celko,

Thanks to you as well, for your valuable suggestions... And I can
understand your outburst.... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...

I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy....

I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...

Thanks once again...

VM
--CELKO-- wrote:
Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.

SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?

A(Lookup table) and B(Transaction Table) <<

Why did you avoid clear names?

CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??

-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.

Can two prefixes belong to the same SAN? No specs given.

Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?

CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);

The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
I hope you get where I am leading this to, from my examples.. <<

No. Clear specs would have been nice, along with real DDL.

Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:

INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@my_foobar_trans_nbr, @my_emp_id,

(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),

Etc.);

You will need error handling code for prefixes that do not match.
Dec 28 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: malcolm | last post by:
Example, suppose you have these 2 tables (NOTE: My example is totally different, but I'm simply trying to setup the a simpler version, so excuse the bad design; not the point here) CarsSold {...
53
by: Kerberos | last post by:
I followed Dan Cederholm's image replacement tutorial, to replace a header tag by a logo. The h1 is clickable if no CSS is applied but it I replace it by the logo, the area isn't clickable anymore...
3
by: Vibha Tripathi | last post by:
Hi Folks, I put a Regular Expression question on this list a couple days ago. I would like to rephrase my question as below: In the Python re.sub(regex, replacement, subject)...
2
by: aj70000 | last post by:
This is my query select ano,max(date),a_subject from MY_TAB where table_name='xyz' and ano=877 group by a_subject,ano order by a_subject ANO max(Date) A_Subject 877 2005-01-20...
59
by: seberino | last post by:
I've heard 2 people complain that word 'global' is confusing. Perhaps 'modulescope' or 'module' would be better? Am I the first peope to have thought of this and suggested it? Is this a...
3
by: testemail | last post by:
Hello How do I perform a variable replacement in ASP.NET when I am using the runat=server clause to generate a table - it was simple in ASP With ASP : ---------- <HTML> .... <BODY>
7
by: Michael | last post by:
Hi Everyone, I'm using VS 2005 now and would like to know if .Net has a replacement for the Resume Next clause. I would like to do something like the following: On Error Resume Next value =...
1
by: lawrence k | last post by:
Want to replace the limit clause in a query, but can't get it right. What's wrong with this: $pattern = "(.*)limit (.*)"; $replacement = '$1'; $replacement .= "LIMIT $limit"; $replacement .=...
3
by: =?Utf-8?B?RHVrZSAoQU4yNDcp?= | last post by:
I've added a web deployment project and want to use the config section replacement but I'm obviously not understanding something. I have set up an alternate appSettings file,...
11
by: endy_tj | last post by:
In vb6 I can use ADO's recordset to retrieve data from database, work on the data, then send the modifications back to the database. I use disconnected recordset, and call updatebatch to send...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.