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
LVL 1
Corey_819Asked:
Who is Participating?
 
geotigerCommented:
Here is an example

SELECT * FROM your_table
WHERE reassign like 'REASSIGN:%'
go
0
 
Craig YellickDatabase ArchitectCommented:
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
0
 
adwisemanCommented:
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
0
 
Corey_819Author Commented:
Hello geotiger, that was exactly what I was looking for. Thanks :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.