asked on

Question on building a query

Hello everyone, I am having a hard time figuring out how to build this query. What I have is I have a field with this data in the field REASSIGN: WO#[43173] - Reassigned from [] to [John Smith] and other data in the field like this MODIFY: WO#[43138] - copier. What I would like to do is build a query that will pull all the records that have the REASSIGN: in the begining of the field. I tried a like but that didn't work. Any ideas on how to do it? Thanks :)

PS Reassigned from [] to [John Smith] This field will be different for each record. Thanks again.

CS

ASKER CERTIFIED SOLUTION

geotiger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Craig Yellick

It's hard to tell exactly what's going on from your description. If you reply with a small amount of sample data, it will help. From what I can see in your question, you have something like this:

REASSIGN MODIFY NAME ITEM
------------- ------------ ------------ ----------------
WO# 43173 WO# 43138 <null> Copier

And you want to update the NAME (and MODIFY?) columns under certain circumstances involving the REASSIGN column.

-- Craig Yellick

adwiseman

I think this is what your looking for, complete with test scripts.

CREATE TABLE tmp(field varchar(100))
INSERT INTO tmp(field)
VALUES('REASSIGN: WO#[43173]')
INSERT INTO tmp(field)
VALUES('MODIFY: WO#[43138]')
CREATE TABLE tmp2(ID INT, id_name varchar(100))
INSERT INTO tmp2(id, id_name)
VALUES(43173, 'John Smith')

GO

Select replace(tmp.field, ISNULL(tmp2.id, ''), ISNULL(tmp2.id_name, '')) as Field
FROM tmp
LEFT OUTER JOIN tmp2 ON substring(tmp.Field, charindex('[', tmp.Field) + 1, charindex(']', tmp.Field) - charindex('[', tmp.Field) - 1) = tmp2.id

Corey_819

ASKER

Hello geotiger, that was exactly what I was looking for. Thanks :)