Click here to Skip to main content
14,639,700 members
Rate this:
Please Sign up or sign in to vote.
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
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

   
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.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100