如何从SQL中的字符串中获取字符？ [英] how to get the character from string in SQL ?

问题描述

我在SQL数据库中有 varchar（max）数据类型的记录，如下所示

ColumnName :: UserID

Row-1 | 1,2,3,4,5,6

第2行| 1,4,5,6

第3行| 1



我需要从该栏目中回复记录，如下所示



select * from Table其中 UserID = 1



但是只显示Row-3



i需要结果，因为所有行都有 1



是否可以在SQLserver ???

解决方案

是的......但这一点都不简单，将它们分成不同的表中的单独行，与外国人分开会更好关键关系回到这一个。问题是SQL字符串处理是......基本的...它不知道逗号分隔的数据，数组，或任何使这项任务更容易的事情。



这将告诉您如何操作：将列中逗号分隔的数据转换为行以供选择 [ ^ ] - 但如果你经常这样做，或者在一大堆数据上，这是一个非常缓慢的过程。



说真的，我会使用另一张桌子。

使用带通配符的命令

< blockquote>没有正确的方法将数字存储为文本。



如果你想从文本中提取数字，请试试这个：

  DECLARE   @ tmp   TABLE （UserID  VARCHAR （ 30 ））
 
 < span class =code-keyword> INSERT   INTO   @ tmp （UserID）
  VALUES （'  1,2,3,4 ，5,6'），（'  1,4,5,6'） ，（'  1'）
 
;  WITH  MyCTE  AS  
（
  SELECT   1   AS  RowNo，UserID  AS  NewUserId， NULL   AS 剩余
  FROM   @ tmp  
  WHERE  CHARINDEX（' ，'，UserID）= 0 
  UNION   ALL  
  SELECT   2   AS  RowNo， LEFT （UserID，CHARINDEX（' ，'，UserID ）-1） AS  NewUserId， RIGHT （UserID，LEN（UserID） -  CHARINDEX（ ' ，'，UserID）） AS 剩余
  FROM   @ tmp  
  WHERE  CHARINDEX（' ，'，UserID）> 0 
  UNION   ALL  
  SELECT  RowNo + 1  AS  RowNo， LEFT （剩余，CHARINDEX（'' ，'，剩余）-1） AS  NewUserId， RIGHT （剩余，LEN（剩余） -  CHARINDEX（' ，'，剩余） ） AS 剩余
  FROM  MyCTE 
  WHERE  CHARINDEX（' ，'，Remainder）> ; 0 
  UNION   ALL  
  SELECT  RowNo + 1  AS  RowNo，剩余 AS  NewUserId， NULL   AS 剩余
  FROM  MyCTE 
  WHERE  CHARINDEX（' ，'，Remainder）= 0 
）
  SELECT  RowNo， CONVERT （ INT ，NewUserID） AS  NewUserID，剩余
  FROM  MyCTE 
   -    OPTION（MAXRECURSION 0） 

结果：

 RowNo NewUID剩余
 1 1 NULL 
 2 1 2,3,4,5,6 
 2 1 4,5,6 
 3 4 5,6 
 4 5 6 
 5 6 NULL 
 3 2 3,4,5,6 
 4 3 4,5,6 
 5 4 5,6 
 6 5 6 
 7 6 NULL 

I have record in SQL database with varchar(max) datatype like as below
ColumnName :: UserID
Row-1 | 1,2,3,4,5,6
Row-2 | 1,4,5,6
Row-3 | 1

Than i need to retrive record from that column like as below

select * from Table where UserID=1

But that only display only Row-3

i need the result as all Rows which have entry 1

is it possible in SQLserver ???

解决方案

Yes...but it's not at all simple, and it would be a much, much better idea to separate those into separate rows in a different table, with a foreign key relationship back to this one. The problem is that SQL string handling is...basic...and it doesn't know about comma separated data, or arrays, or anything which would make this task easier.

This shows you how to do it: Converting comma separated data in a column to rows for selection[^] - but if you do it often, or on a large set of data, it's a very slow process.

Seriously, I'd use another table.

use like command with wildcards

There isn't proper way to store numbers as a text.

If you want to extract numbers from text, please try this:

DECLARE @tmp TABLE (UserID VARCHAR(30))

INSERT INTO @tmp (UserID)
VALUES('1,2,3,4,5,6'), ('1,4,5,6'), ('1')

;WITH MyCTE AS
(
	SELECT 1 AS RowNo, UserID AS NewUserId, NULL AS Remainder 
	FROM @tmp
	WHERE CHARINDEX(',',UserID)=0
	UNION ALL
	SELECT 2 AS RowNo, LEFT(UserID,CHARINDEX(',',UserID)-1) AS NewUserId, RIGHT(UserID,LEN(UserID) - CHARINDEX(',', UserID)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',',UserID)>0
	UNION ALL
	SELECT RowNo+1 AS RowNo, LEFT(Remainder,CHARINDEX(',',Remainder)-1) AS NewUserId, RIGHT(Remainder,LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM MyCTE
	WHERE CHARINDEX(',',Remainder)>0
	UNION ALL 
	SELECT RowNo+1 AS RowNo, Remainder AS NewUserId, NULL AS Remainder
	FROM MyCTE 
	WHERE CHARINDEX(',',Remainder)=0
)
SELECT RowNo, CONVERT(INT,NewUserID) AS NewUserID, Remainder 
FROM MyCTE
--OPTION (MAXRECURSION 0)

Result:

RowNo  NewUID Remainder
1      1      NULL
2      1      2,3,4,5,6
2      1      4,5,6
3      4      5,6
4      5      6
5      6      NULL
3      2      3,4,5,6
4      3      4,5,6
5      4      5,6
6      5      6
7      6      NULL

这篇关于如何从SQL中的字符串中获取字符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！