Click here to Skip to main content
14,970,229 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

I have fetched a data from DB and now I need to seperate that data in the same sql query. how wud i do it.

e.g. - suppose my sql query fetch a data " prnt_key = ordr_id | ordr_typ " or sometimes " prnt_key = ordr_id | ordr_typ | deal_id " . there may be 2-3 values seperated by pie. Now i need to seperate that ordr_id & ordr_typ or that 3rd value in the same sql query. like my sql query fetch that " prnt_key " and seperate all that data and show them in aliasing column. hw wud i do that.... is there any way of doing it.
Posted
Comments
Arunprasath Natarajan 14-Jun-12 2:30am
   
You can use charIndex
punkiv 14-Jun-12 2:42am
   
cud u give an example, to split it on basis of " | " .....

Hi,

Fetching the data is not a big deal. You can fetch the data in any format. Now the question is you are using a string column to hold the data of three respective columns. So, here you can use substring function of sql server to solve your problem.
Refer these links:
http://msdn.microsoft.com/en-us/library/ms187748.aspx[^]
http://msdn.microsoft.com/en-us/library/aa259342%28v=sql.80%29.aspx[^]
http://www.1keydata.com/sql/sql-substring.html[^]

All the best.
--AK
   
i am giving you a function which will work as split function. and return a table

SQL
--select * from computds.dbo.split('CON|CON1|CON2','|')
Create FUNCTION [dbo].[Split](@String varchar(max), @Delimiter char(1))
returns @temptable TABLE (items varchar(max))
as
begin
    declare @idx int
    declare @slice varchar(max)

    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

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

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end
   
Comments
punkiv 14-Jun-12 3:20am
   
how wud i use this function in my sql query...

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900