How to split the values in sqlserver 2008

Question

0.00/5 (No votes)

See more:

Hi,

Vessl Name:PAC BINTAN,
Voyage No:01,
IGM No.:C-95/12,
EGM No.:C-108/12,
Type of Voyage - Coastal/foreign:COASTAL RUN,
Port of Call :VISAKHAPATNAM,
Clients Name:LEIGHTON CONTRACTORS(INDIA)LTD

I select data from the table as above.Then, How to split the above underline values and return in a table(Like below) form in sqlserver 2008.

Result:

PAC BINTAN
01
C-95/12
C-108/12
COASTAL RUN
VISAKHAPATNAM
LEIGHTON CONTRACTORS(INDIA)LTD

if any one know about this tell me.

Regards
Nanda Kishore.CH

Posted 2-Oct-12 19:14pm

nandkishorre

Updated 2-Oct-12 19:22pm

v2

Add a Solution

Comments

Mehdi Gholam 3-Oct-12 1:18am

Your question is not clear.

nandkishorre 3-Oct-12 1:28am

I selectd data from the database as i wrote above with colon(:). i need to split the colon(:) data like as above shown below Result tag in sqlserver 2008.

Abhijit Parab 3-Oct-12 1:28am

Please explain your question in detail.

nandkishorre 3-Oct-12 1:29am

I selectd data from the database as i wrote above with colon(:). i need to split the colon(:) data like as above shown below Result tag in sqlserver 2008.

Zoltán Zörgő 6-Oct-12 2:00am

Just a question: why don't you handle this on client side? It is far more easier, but I suppose, your presentation method is bound to datasets. As this is a structured data I would store it in a serialized form at the first place. SQL server has XML support, but you can use JSON serialization also, that is more adequate in several situations.

nandkishorre 6-Oct-12 2:45am

I have lakhs of records in my table.. it takes nearly 40 secs for splitting the data in client side. that's y i wrote function in database. it takes 3 to 4 secs only for my application performance.

Zoltán Zörgő 6-Oct-12 6:05am

Something must be wrong with your code or your client is really low profile if this is the case. Anyway be sure to plan your application for the real-life environment, not your development one: if this is so time-consuming, will this not be a huge performance problem for the server under heavy load? According to what you have posted, I think your data will be inserted/updated rarely, but selected often. If you store your data this way, you will have to split it really often. Would't be easier to store such details directly in a table?
I suggest you considering the following approach: create detail table for these attributes, but if you need it also in this form (for quick presentation, I suppose), store it as a text field also. You loose 3MF, but that's not a big deal if you manage the consistence. You could do this split once on insert and on update if necessary.

4 solutions

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)

SruthiR · Answer 1 · 2012-10-02T19:42:00

SQL

Function
	@listString VARCHAR(8000),
	@Delimeter char(1)


RETURNS @ValueTable table 
(			
	Value VARCHAR(8000)
) 

AS
BEGIN

	DECLARE @NextString VARCHAR(8000)
	DECLARE @Pos INT
	DECLARE @NextPos INT
	DECLARE @String VARCHAR(8000)
	DECLARE @Delim VARCHAR(1)

	SET @NextString = ''
	SET @String = @listString
	SET @Delim = @Delimeter
	SET @String = @String + @Delim
	SET @Pos = charindex(@Delim,@String)

	WHILE (@pos <> 0)
		BEGIN
			SET @NextString = substring(@String,1,@Pos - 1)	
			INSERT INTO @ValueTable (Value) Values (@NextString)		
			SET @String = substring(@String,@pos+1,len(@String))
			SET @pos = charindex(@Delim,@String)			
		END 
		
	RETURN

END
GO

pass delimeter value as ':'.

Mehdi Gholam · Answer 2 · 2012-10-02T19:38:00

Solution 1

Read the following :
http://stackoverflow.com/questions/2507330/sql-server-split-operation[^]

http://stackoverflow.com/questions/1869465/indexof-function-in-t-sql[^]

Posted 2-Oct-12 19:38pm

Mehdi Gholam

damodara naidu betha · Answer 3 · 2012-10-03T00:06:00

Solution 3

Hi Nanda Kishore,

Try this code block

SQL

WITH SplitCTE AS (
SELECT CAST('<i>' + REPLACE(DelimitedColumn, ',','</i><i>') + '</i>' AS     XML) AS Strings
FROM YourTableName WHERE <CONDITION>
)

-- Xquery to get the desired result set using CROSS JOIN
SELECT  SUBSTRING(x.i.value('.', 'VARCHAR(MAX)'),CHARINDEX(':',x.i.value('.',                                                 'VARCHAR(MAX)'))+1,LEN(x.i.value('.', 'VARCHAR(MAX)'))) AS Strings
FROM SplitCTE CROSS APPLY Strings.nodes('//i') x(i)

Posted 3-Oct-12 0:06am

damodara naidu betha

Updated 3-Oct-12 0:09am

v2

Comments

nandkishorre 3-Oct-12 6:54am

What is x.i.value here ?

damodara naidu betha 3-Oct-12 7:12am

Hi .. we have converted the columns values into XML Elements in the CTE. So each column value becomes an element value in the XML. To access this value we use x.i.value ( In Xquery)

nandkishorre · Answer 4 · 2012-10-05T19:51:00

SQL

CREATE FUNCTION [dbo].[SplitReferenceDetails](@BranchID int)
returns @temptable TABLE (ReferenceType varchar(8000))
as
begin
   declare @idx int
   declare @slice varchar(8000)
   Declare @string varchar(max)
   Declare @JobID int
   Declare @Reference varchar(max)
   Declare @Delimiter char(1)
   set @Delimiter=','


  IF(@BranchID IS NOT NULL AND @BranchID != 0 )
  BEGIN
       Declare  tblReferences cursor
       for

      select JobID from tbljobdetails where BranchID=@BranchID
   END
     ELSE
  BEGIN
       Declare  tblReferences cursor
       for

      select JobID from tbljobdetails
   END

      open tblReferences
            fetch next from tblReferences into @JObId
    while (@@Fetch_status>=0)
    begin
                select @string = (select Reference from tbljobdetails where jobid=@JObId)
      select @idx = 1
        --if len(@String)<1 or @String is null   return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
           set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

      set @slice= (Select  SubString(@slice,CharIndex(':', @slice) + 1,200))


        if(len(@slice)>0)
            insert into @temptable(ReferenceType) values(@slice)

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
    fetch next from tblReferences into @JObId
    end
    close tblReferences
    deallocate tblReferences

return
end