Split string on dash without using stored procedure

Question

4.00/5 (1 vote)

See more:

I've written this, and it works, but it's not dynamic, which it needs to be as the delimiter (-) will be in different positions each time.

use "database";
select substring(comment, 0, 5) as col1,
substring(comment, 6, 5) as col2,
substring(comment, 12, 5) as col3
from tillsummaries
where datalength(comment) > 0

By using the substring function, it will allow me to select the column of info I want, tell it the start position, and length, but I'm not sure on how to use variables.

The way I think I need to do this (and please tell me if I'm totally wrong) is I need to have 3 different variables.

@start
@length
@separator

@start = 0

SQL would then need to count to the next @seperator to find the length.
This would then set @length.

Once it's done that I think the statement should go something like this

select substring(comment, @start, @length)

But that's where I lose it and get confused, as generally with .net I would simply type an if statement to run through each line until the delimiter, place that var in an array and assign that to a table or something of that nature.

If I use the above select statement example, I have no idea how I could adjust the col2 and col3 @start and @length properties as there would be multiple rows.

Posted 31-Aug-15 17:15pm

Mendaharin

Add a Solution

Comments

jgakenhe 31-Aug-15 23:48pm

Ah, the multivalued attribute problem, how lovely. Well, I first look at this and think you have to iterate through each value and split it. That leads me to think cursor and then look for the delimiter. Cursors can be real slow in SQL Server, especially with large data sets. The second option would be to use a while loop and look for then split based upon the character index.

After a quick google search, I think this is your answer.

http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/

Mendaharin 1-Sep-15 0:02am

Have looked at that before. Unfortunately I can't use stored procedures, which is why I was looking at some way to do it through a simple select query.
I know what you mean with multivalue stuff though. This is so much easier in visual studio!!!

3 solutions

Solution 1

Use @charindex. Like @charindex(comment,'-').

Posted 31-Aug-15 17:39pm

chairborne82

Comments

Mendaharin 1-Sep-15 0:08am

Doesn't quite work. still comes up with more than 1 value. But, from what I understand of charindex, it will allow me to count up to the first -.

Which is what I think I need, how would I then assign charindex location to a variable at runtime though? and then to re-iterate through the following -'s to the endline. So charindex would theoretically need to be reset after each value has been found.

chairborne82 1-Sep-15 13:44pm

Why are you doing this

Solution 2

I understand that you don't want to use a procedure but in my opinion using a function would make this much more easier. The advantages of using a function is that you don't need to add excessive logic to the statement and the same block of code can be reused everywhere.

If interested, there are a lot of examples how to build a split function. For example An Easy But Effective Way to Split a String using Transact-SQL[^] or http://sqlmag.com/site-files/sqlmag.com/files/archive/sqlmag.com/content/content/21071/listing_01.txt[^]

Posted 31-Aug-15 18:29pm

Wendelius

Updated 31-Aug-15 18:31pm

v2

Comments

Mendaharin 1-Sep-15 0:51am

unfortunately the software that we use won't allow us to actually call a stored procedure unfortunately. It's starting to look like I'm going to need to write a simple VB form that will allow them to do this, we are very restricted with what we can do within the software. But we do have full access to the DB

Wendelius 1-Sep-15 1:01am

You do understand that I'm not referring to stored procedures?

What I suggested is usage of a function which would be used in a normal SELECT statement just like you use for example SUBSTRING.

So if your software allows you to call SUBSTRING, I can't see any reason why it wouldn't allow you to call any other function.

Mendaharin 1-Sep-15 1:39am

I'm sorry I'm just starting out with more advanced SQL coding, so I wasn't aware of your original post intent.
But, in saying, looks like our software won't allow the creation of functions either prior to the routine.

Wendelius 1-Sep-15 2:52am

Functions are typically not created by the program. They are permanent objects like tables that can be used inside a SQL statement. Just like the SUBSTRING in your example.

Have a look at the examples in MSDN: https://msdn.microsoft.com/en-us/library/ms191320.aspx[^].
I think it will explain the usage of basic functions quite nicely.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jaket-cp · Accepted Answer · 2015-09-01T02:32:00

If you know only two delimiters will be within your column result.
You could use TSQL string functions in conjunction to get your desired result.

Here is a bit of SQL that will do the job.

SQL

with TabDelimOfValue as (
--setup dummy data
	select '123-456-789' val
	union select '12-345-6789'
	union select '1-234-56789'
	union select '1-2345-6789'
	union select '1-23456-789'
	union select '1-234567-89'
	union select '1-2345678-9'
)
select 
	charindex('-', val) delimPos1,
	len(val) - charindex('-', reverse(val)) + 1 delimPos2,
	substring(val, 0, charindex('-', val)) Sub1,
	substring(val, (charindex('-', val) + 1), (len(val) - charindex('-', reverse(val)) - charindex('-', val))) Sub2,
	substring(val, len(val) - charindex('-', reverse(val)) + 2, (charindex('-', reverse(val)) - 1)) Sub3,
	val
from TabDelimOfValue;

Even though it can be done this way, it is hard to understand what is happening in the statement and will be difficult to maintain.

I would suggest using a user defined function as specified in solution 2 if possible.

I read an article the other day of a udf which does what you need, if I find it I will add it here.

Hope that helps out.

EDIT
Found the link - seems like you may have read this one already, it was a solution to one of your other questions.
But here it is:
http://social.technet.microsoft.com/wiki/contents/articles/26937.t-sql-splitting-a-string-into-multiple-columns.aspx[^]

Split string on dash without using stored procedure

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us