Click here to Skip to main content
15,905,875 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i have a table in which data comes like this:
claimid amount drive
1 10/11 car/bike
2 11 car

i want to show data on UI based on claim id: if there any slace then remove it:
claimid  amount     drive 
1          10        car
           11        bike     
             
2          11        car


What I have tried:

i dnt know how to implememnt this as i never face such condition
Posted
Updated 5-May-17 10:33am
Comments
[no name] 5-May-17 8:21am    
What is a "slace"?
Member 12300036 8-May-17 0:10am    
slash*
Richard Deeming 5-May-17 14:00pm    
Sounds like an incredibly bad database design. Multiple fields in a single column is always a bad idea.

You should normalize your database - you need at least two tables:
Claims (claimid : primary key)
ClaimDetails (claimid : foreign key, amount, drive)

Your data would then become:
Claims:
=======
claimid
-------
1
2

ClaimDetails
============
claimid  amount  drive
----------------------
1        10      car
1        11      bike
2        11      car

which is already pretty close to your desired output.

You can split the string in SQL based on that character. There are a few ways of doing that. This one uses FOR XML ... Splitting Delimited Strings Using XML in SQL Server[^]
And this one (which is the one I use) actually uses a WHILE loop (but don't tell anyone!)... How to Split a string by delimited char in SQL Server.............. - SQLServerCentral[^]
The problem then becomes how to merge the data in the way you want. I ended up with this really awful query
SQL
declare @tab table (claimid int, amount nvarchar(50), drive nvarchar(50))
declare @inc int = 1
declare @top int = (SELECT MAX(claimid) FROM ClaimDetails) 
WHILE @inc <= @top
BEGIN
	declare @amt nvarchar(50), @drv nvarchar(50)
	SELECT @amt = amount, @drv = drive FROM ClaimDetails WHERE claimid = @inc

	declare @tab1 table (rn int identity(1,1), claimid int, amount nvarchar(50))
	declare @tab2 table (rn int identity(1,1), claimid int, drive nvarchar(50))

	INSERT INTO @tab1 SELECT @inc, splitdata as amount FROM dbo.fnSplitString(@amt, '/') 
	INSERT INTO @tab2 SELECT @inc, splitdata as amount FROM dbo.fnSplitString(@drv, '/') 

	INSERT INTO @tab
	SELECT @inc, amount, drive
	FROM @tab1 T1
	LEFT JOIN @tab2 T2 ON T1.claimid=T2.claimid AND T1.rn=T2.rn

	delete @tab1
	delete @tab2

	SET @inc += 1
END 

SELECT * FROM @tab

It gets the results you want, but I think that process is best left to your UI layer if at all possible

[UPDATE]
As requested, here is an explanation of this query step by step

I declared a table variable @tab to hold the final results. It should be identical in form to your ClaimDetails table.

Next up are variables @inc(short for "increment") and @top (short for "top value of claimed"). While writing this explanation I realised that I had @top = COUNT... but I should be reading the MAX value - I've corrected the code above. Those variables just control the WHILE loop which is going to step through the entire ClaimDetails table row by row (loops are not usually necessary but your requirements are somewhat unique).

Inside that loop we are grabbing some information from each row, one row at a time... so first time through the loop the line
SQL
SELECT @amt = amount, @drv = drive FROM ClaimDetails WHERE claimid = @inc
gets the row for claimid = 1 and sets the variable @amt to the value 10/11 and @drv to the value car/bike.

We can then pass those values into the String split function from the link I included above. I'm not going to explain that as the author does that himself. The main point is that we can't do this in a SELECT statement directly because the function returns a table of results

The table variables @tab1 and @tab2 will contain the results from splitting those strings. The rn column on each will automatically assign a "row number" which we will use later to match up the '10' with 'car' and the '11' with 'bike'.

So after the first loop @tab1 contains
rn   claimid  amount
1    1        10
2    1        11
and @tab2 contains
rn   claimid  drive
1    1        car
2    1        bike
The next bit takes these two tables and matches up rn = 1 on @tab1 (value '10') with rn = 1 on @tab2 (value 'car') and then rn = 2 (value '11') with rn = 2 on @tab2 (value 'bike'). To be honest, you don't actually need T1.claimid=T2.claimid because we're in a loop and the only data we have is from a single claimid for each iteration of the loop.

So after the first loop @tab contains
claimid amount  drive
1	10	car
1	11	bike
You have to delete the contents of the table variables for each loop otherwise the data gets doubled up - even though the variables are declared within the loop!

Obviously don't forget to move the loop counter on
SQL
SET @inc += 1
to avoid an infinite loop. That line is the same as
SQL
SET @inc = @inc + 1
When the loop is complete the table variable @tab contains the data split the way you described in your post
claimid  amount     drive 
1          10        car
1          11        bike     
             
2          11        car
Note that the second row also contains the claimid 1. You cannot return results the exact way you described in your post, you definitely have to handle that in the UI layer.
 
Share this answer
 
v2
Comments
Member 12300036 11-May-17 4:31am    
how its working..its not permanenet solution
CHill60 11-May-17 4:43am    
I don't understand this comment.
I also don't understand why you have accepted a solution that does not produce the results you asked for
Member 12300036 11-May-17 5:32am    
any other solution for this?
CHill60 11-May-17 5:45am    
If you want it in SQL then the above is the only way I could think of. I've already suggested doing it in the UI layer and Richard Deeming has suggested a way of improving your database. There may well be another solution, but I can't think of it.
You said earlier "how its working..its not permanenet solution" - what did you mean? Do you want an explanation of how it works?
Member 12300036 11-May-17 5:59am    
yeeah
Hi,

based on my understanding on your question, you can try this snippet for removing back slash between your column data

SELECT CASE
         WHEN ColumnName LIKE '%/%' THEN LEFT(ColumnName, Charindex('/', ColumnName) - 1)
         ELSE ColumnName
       END
 
Share this answer
 
Comments
CHill60 6-May-17 10:03am    
But if column amountcontains '10/11' and column drive contains 'car/bike' how does the OP get
1 10 car
1 11 bike
with this method?
The14thNoah 8-May-17 10:50am    
can you give me sample data from your table? and sample query as well?
CHill60 8-May-17 11:48am    
I'm not the OP. The OP has provided some sample data and the expected results in their question.
Member 12300036 10-May-17 7:46am    
already describe everything in question

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