如何在SQL中的表列中删除两个值之间的正向slace [英] How to remove forward slace between two value in a table column in SQL

问题描述

我有一张表格，其数据如下：

claimid金额驱动器

1 10/11汽车/自行车

2 11汽车



i希望根据声明ID在UI上显示数据：如果有任何slace则删除它：

索赔金额
 1 10 
 $ 11 
 
 $ 11 $ 我尝试了什么： 
 
 
 
i知道如何实现这一点，因为我从未遇到过这种情况
解决方案
 
您可以根据该字符在SQL中拆分字符串。有几种方法可以做到这一点。这个使用FOR XML ... 使用XML拆分分隔字符串在SQL Server中 [ ^ ] 
 
这个（我使用的那个）实际上使用WHILE循环（但不要告诉任何人！）... 如何在SQL Server中按分隔字符串拆分字符串..............  -  SQLServerCentral  [ ^ ] 
 
然后问题就变成了如何以你想要的方式合并数据。我最终得到了这个非常糟糕的查询
 声明  @ tab  表（claimid  int ，金额 nvarchar （ 50 ），drive  nvarchar （ 50 ））
 声明  @ inc   int  = < span class =code-digit> 1  
 声明  @ top  < span class =code-keyword> int  =（ SELECT  MAX（claimid） FROM  ClaimDetails）
  WHILE   @ inc < =  @top  
  BEGIN  
 声明  @ amt   nvarchar （ 50 ）， @ drv   nvarchar （ 50 ）
  SELECT   @ amt  =金额， @ drv  = drive  FROM  ClaimDetails  WHERE  claimid =  @ inc  
 
 声明  @ tab1   table （rn  int   identity （< span class =code-digit> 1 ， 1 ），claimid  int ，金额 nvarchar （ 50 ））
 声明  @ tab2   table （rn  int   identity （ 1 ， 1 ），claimid  int ，drive  nvarchar （ 50 ））
 
  INSERT   INTO   @ tab1   SELECT   @ inc ，splitdata < span class =code-keyword> as  amount  FROM  dbo.fnSplitString（ @ amt  ，'  /'）
  INSERT   INTO   @ tab2   SELECT   @ inc ，splitdata  as  amount  FROM  dbo.fnSplitString（ @ drv ，'  /'）
 
  INSERT   INTO   @ tab  
  SELECT   @ inc ，金额，驱动器
  FROM   @ tab1  T1 
  LEFT   JOIN   @ tab2  T2  ON  T1.claimid = T2.claimid  AND  T1.rn = T2.rn 
 
 删除  @ tab1  
  delete   @ tab2  
 
  SET   @inc  + =  1  
  END  
 
  SELECT  *  FROM   @ tab  
 
 
它可以获得你想要的结果，但我认为这个过程最好留给你的UI层，如果可能的话
 
 
 
 [更新] 
 
根据要求，这里是一步一步解释这个查询
 
 
 
我声明了一个表变量 @tab 保存最终结果。它应该与您的ClaimDetails表格相同。
 
 
 
接下来是变量 @inc （的缩写增量）和 @top （声明的最高值的缩写）。在写这个解释时，我意识到我有@top = COUNT ...但我应该读取MAX值 - 我已经纠正了上面的代码。这些变量只是控制WHILE循环，它将逐行遍历整个ClaimDetails表（循环通常不是必需的，但你的要求有点独特）。
 
 
 
在那个循环中，我们从每一行抓取一些信息，一次一行 ...所以第一次通过循环
  SELECT   @ amt  =金额， @ drv  = drive  FROM  ClaimDetails  WHERE  claimid =  @ inc  
获取claimid = 1的行并将变量 @amt 设置为值 10/11 和@drv值汽车/自行车。
 
 
 
我们可以将这些值传递给链接I中的String split函数包含在上面。我不打算解释，因为作者自己这样做。重点是我们不能直接在 SELECT 语句中执行此操作，因为该函数返回结果的表 
 
 
 
表变量 @ tab1 和 @ tab2 将包含拆分的结果那些字符串。每个 rn 列会自动指定一个行号，稍后我们将使用该行号将'10'与'car'匹配，'11'与'bike'匹配。 
 
 
 
所以在第一个循环之后 @ tab1 包含
 rn claimid amount 
 1 1 10 
 2 1 11 
和 @ tab2 包含
 rn claimid drive 
 1 1汽车
 2 1辆自行车
下一位采用这两个表并匹配@ tab1上的rn = 1（值'10'），@ tab2上的rn = 1（值' car'）然后rn = 2（值'11'），其中rn = 2 on @ tab2（值'bike'）。说实话，你实际上并不需要 T1.claimid = T2.claimid 因为我们处于一个循环中，我们拥有的唯一数据来自每个循环的迭代。
 
 
 
所以在第一个循环@tab包含
 claimid金额驱动器
 1 10 car 
 1 11 bike 
你必须为每个循环删除表变量的内容，否则数据会加倍 - 即使变量是在循环中声明的！
 
 
 
显然不要忘记在
  SET   @inc  + =  1  
以避免无限循环。该行与
  SET   @ inc  =  @ inc  +  1  
当循环完成时，表变量 @选项卡包含按照您在帖子中描述的方式分割的数据
 claimid amount drive 
 1 10 car 
 1 11 bike 
 
 2 11 car 
请注意，第二行还包含claimid  1 。你不能以你在帖子中描述的方式返回结果，你必须在UI层中处理它。
 
 
你好，
 
 
 
基于我对您的问题的理解，您可以尝试使用此代码段删除列数据之间的反斜杠
 
 
 
 
 SELECT CASE 
 WHEN ColumnName LIKE'％/％'然后左（ColumnName，Charindex（'/'，ColumnName） -  1）
 ELSE ColumnName 
 END 
 
 
 
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
解决方案
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
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 
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 
SET @inc += 1
to avoid an infinite loop. That line is the same as 
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.

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


                        
这篇关于如何在SQL中的表列中删除两个值之间的正向slace的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！