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:

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.
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 " | " .....


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:[^][^][^]

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

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

    select @idx = 1
        if len(@String)<1 or @String is null  return

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

            insert into @temptable(Items) values(@slice)

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
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