Question on building a query

Posted on 2005-05-02
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

    Here is an example

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

    Expert Comment

    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

    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')


    Select replace(tmp.field, ISNULL(, ''), 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) =
    LVL 1

    Author Comment

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

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now