Splitting text in SQL Server stored procedure

Question

I'm working with a database, where one of the fields I extract is something like:

1-117 3-134 3-133

Each of these number sets represents a different set of data in another table. Taking 1-117 as an example, 1 = equipment ID, and 117 = equipment settings.

I have another table from which I need to extract data based on the previous field. It has two columns that split equipment ID and settings. Essentially, I need a way to go from the queried column 1-117 and run a query to extract data from another table where 1 and 117 are two separate corresponding columns.

So, is there anyway to split this number to run this query?

Also, how would I split those three numbers (1-117 3-134 3-133) into three different query sets?

The tricky part here is that this column can have any number of sets here (such as 1-117 3-133 or 1-117 3-134 3-133 2-131).

I'm creating these queries in a stored procedure as part of a larger document to display the extracted data.

Thanks for any help.

Data with different meanings should be in different columns and/or different rows. — Ghost, Jul 12 '12 at 23:24
There is a way to do what you want, but you should really normalize your data structure — Ghost, Jul 12 '12 at 23:25
I understand. The current system is very awkward. But I'm just an intern, and I don't think I can change it. — J T, Jul 12 '12 at 23:26
Are the numbers always delimited with a space? Are the smaller numbers always in a #-### format? — Holger Brandt, Jul 12 '12 at 23:27
Yes, the format is constant. #-### followed be a space if there is another set. — J T, Jul 12 '12 at 23:28
Are you talking about SQL Server T-SQL, or Oracle PL/SQL, or some other vendor's implementation? It will make a huge difference, and I'm sure if you provide the DBMS you're working with I could answer your question, or if not, someone else can. — Jim, Jul 12 '12 at 23:33
Not sure that the function you look for could be part of SQL standard. What database are you using? — madth3, Jul 12 '12 at 23:33
You will need to use a stored procedure for this one. Try this link: http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str — Holger Brandt, Jul 12 '12 at 23:36
I'm not sure if this answers your question, but we're using a Microsoft SQL server, and I'm using Visual Studio 2010 to manage. — J T, Jul 12 '12 at 23:46
What version of SQL Server? 2000, 2005, 2008, 2012? Or God forbid, 7.0, 6.5, 6.0? — ErikE, Jul 13 '12 at 05:13

score 1 · Accepted Answer · edited May 23 '17 at 11:48

Since you didn't provide the DB vendor, here's two posts that answer this question for SQL Server and Oracle respectively...

T-SQL: Opposite to string concatenation - how to split string into multiple records

Splitting comma separated string in a PL/SQL stored proc

And if you're using some other DBMS, go search for "splitting text ". I can almost guarantee you're not the first one to ask, and there's answers for every DBMS flavor out there.

As you said the format is constant though, you could also do something simpler using a SUBSTRING function.

EDIT in response to OP comment...

Since you're using SQL Server, and you said that these values are always in a consistent format, you can do something as simple as using SUBSTRING to get each part of the value and assign them to T-SQL variables, where you can then use them to do whatever you want, like using them in the predicate of a query.

You're right, I copy/pasted from the wrong browser tab. I've edited it. — Jim, Jul 13 '12 at 19:13

score 1 · Answer 2 · answered Jul 13 '12 at 04:07

Assuming that what you said is true about the format always being #-### (exactly 1 digit, a dash, and 3 digits) this is fairly easy.

WITH EquipmentSettings AS (
   SELECT
      S.*,
      Convert(int, Substring(S.AwfulMultivalue, V.Value * 6 - 5, 1) EquipmentID,
      Convert(int, Substring(S.AwfulMultivalue, V.Value * 6 - 3, 3) Settings
   FROM
      SourceTable S
      INNER JOIN master.dbo.spt_values V
         ON V.Value BETWEEN 1 AND Len(S.AwfulMultivalue) / 6
   WHERE
      V.type = 'P'
)
SELECT
   E.Whatever,
   D.Whatever
FROM
   EquipmentSettings E
   INNER JOIN DestinationTable D
      ON E.EquipmentID = D.EquipmentID
      AND E.Settings = D.Settings

In SQL Server 2005+ this query will support 1365 values in the string.

If the length of the digits can vary, then it's a little harder. Let me know.

score 0 · Answer 3 · answered Jul 13 '12 at 01:00

Incase if the sets does not increase by more than 4 then you can use Parsename to retrieve the result

 Declare @Num varchar(20)
 Set @Num='1-117 3-134 3-133'

 select parsename(replace (@Num,' ','.'),3)

 Result :- 1-117

 Now again use parsename on the same resultset 

 Select parsename(replace(parsename(replace (@Num,' ','.'),3),'-','.'),1)

 Result :- 117

If the there are more than 4 values then use split functions

Splitting text in SQL Server stored procedure

3 Answers3