SQL Server Stored Proc Comma Delimited Field Question

Posted on 2005-05-05
Last Modified: 2010-03-19
Hi. Im using SQL Server 2000.  I am tring to write a stored procedure to do the following:
basically im trying to retrieve data from the db tables using a SQL string.  However in the where clause I need all the records in Table A where the 'State' abbreviation field matches one of the states in the 'Taxing_States' field of Table B for the specified Company Number

Table A:
Order_Number   Company_Number    City           State
001                   1000                       New York   NY
002                   1001                       San Jose    CA
003                   1001                       Waldorf      MD
004                   1000                       Baltimore    MD

Table B:
Company_Number       Taxing_States
1000                          NY, VA, TX
1001                          NY, MD, IO, VA

So the SQL statement should return Order Numbers 001 and 003

I would make a table where each line has the company number and 1 state abbreviation but this stuff was already made when i got here and it would be ALOT of records ro redo.

Basically the question comes down to is, is there a way to parse through a character field thats comma delimited and match it to another field in a stored procedure


Question by:spfeedback82
    LVL 13

    Accepted Solution

    select A.Order_Number
    from TableA A
    join TableB B ON A.Company_Number=B.Company_Number and ','+B.Taxing_States+',' like '%,'+A.State+',%'
    LVL 68

    Assisted Solution

    Since states are always exactly two chars and always unique, I think you only have to do this:

    SELECT A.Order_Number --, ...
    FROM tableA A
    INNER JOIN tableB B ON B.Company_Number = A.Company_Number AND CHARINDEX(A.State, B.Taxing_States) > 0
    LVL 13

    Expert Comment

    I have 2 questions.
    1. Why you did not give any response for 25 days?
    2. Why all your questions are rated B?

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now