Split strings out of one long string from a single column

Question

0.00/5 (No votes)

See more:

I've dealt with string splits and dilimeters in VB and seems very easy and straight through, but I'm a little bit unsure as to how to do it within a T-SQL script.

I have a column of information on a table. We only have 1 that we can put information into, but currently we put about 4 different bits of info in it, with a '-' delimiter.

I have nfi how to write the script to do this though.
Currently what I have is this via the MSDN

SELECT SUBSTRING(comment, CHARINDEX('', comment) + 1, 100) from tillsummaries

Which does allow me to split a string into one column, but if I put in the next string that I want to be separated(create another select statement with criteria required), it seems to be created on a different table.
(I'm guessing this is because I am actually writing 2 select statements, so they are separate queries)

How am I able to use the substring function to do the following.

Example info comment = "home-55041213-qld"

would like the information to stay in the one table so it would be
col1 = home, col2 = 55041213 col3 = qld

Posted 25-Aug-15 20:42pm

Mendaharin

Add a Solution

Comments

Schatak 26-Aug-15 7:16am

What I understand is you want to combine 3 database columns into a Single column ?
these col1, col2 and col3 are in one table?

1 solution

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)

RISKYROCKS · Answer 1 · 2015-08-25T21:33:00

Solution 1

CHECK THIS LINK I THINK IT HELPS YOU A LOT

http://social.technet.microsoft.com/wiki/contents/articles/26937.t-sql-splitting-a-string-into-multiple-columns.aspx[^]4

Posted 25-Aug-15 21:33pm

RISKYROCKS

v2

Comments

Mendaharin 27-Aug-15 1:53am

Can I ask a stupid question. I'm used to programming in VB, so I'm a little confused on how programming actually works within SQL.

I've written this, and it seems to work, but obviously it's not dynamic, which it kinda needs to be as the delimiter will be in different positions each time.

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 seperate dash and tell it where to go.

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.