[Last Call] Learn how to a build a cloud-first strategyRegister Now


SQL Server Stored Proc Comma Delimited Field Question

Posted on 2005-05-05
Medium Priority
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
  • 2
LVL 13

Accepted Solution

ispaleny earned 260 total points
ID: 13939135
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 260 total points
ID: 13939695
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

ID: 14106965
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

829 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