Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Question on building a query

Posted on 2005-05-02
Medium Priority
Last Modified: 2011-09-20
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.

Question by:Corey_819
LVL 12

Accepted Solution

geotiger earned 500 total points
ID: 13908598
Here is an example

SELECT * FROM your_table
WHERE reassign like 'REASSIGN:%'
LVL 11

Expert Comment

by:Craig Yellick
ID: 13908623
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
LVL 14

Expert Comment

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

CREATE TABLE tmp(field varchar(100))
INSERT INTO tmp(field)
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')


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

Author Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question