Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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
Edited 2-Oct-12 19:22pm
v2
Comments
Mehdi Gholam at 3-Oct-12 1:18am
   
Your question is not clear.
nandkishorre at 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 at 3-Oct-12 1:28am
   
Please explain your question in detail.
nandkishorre at 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ő at 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 at 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ő at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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 ':'.
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi Nanda Kishore,

Try this code block
 
 
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)
 
  Permalink  
v2
Comments
nandkishorre at 3-Oct-12 6:54am
   
What is x.i.value here ?
damodaranaidu.betha at 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)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 305
1 _Amy 235
2 Peter Leow 205
3 Andreas Gieriet 180
4 Dave Kreskowiak 130
0 OriginalGriff 7,540
1 Sergey Alexandrovich Kryukov 6,412
2 Maciej Los 3,849
3 Peter Leow 3,653
4 CHill60 2,712


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 6 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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