2024-10-24 07:25:03
取倒数第二个逗号和倒数第一个逗号的值是么?方法很多,举个例子:
with tmp(col) as(
select '123,3,3,56,77,233'
union all
select ',1,4,5,21,43,'
)
select reverse(substring(col,
CHARINDEX(',', col) + 1,
CHARINDEX(',', col, CHARINDEX(',', col) + 1) -
CHARINDEX(',', col) - 1))
from (select reverse(col) as col from tmp) t
如果只有一个逗号可以考虑CTE递归或者PLSQL:
with tmp(col) as(
select '77,233'
union all
select ',1,4,5,21,43,'
),
roy AS
(SELECT col =
CAST(LEFT(col, CHARINDEX(',', col + ',') - 1) AS NVARCHAR(100)), Split = CAST(STUFF(col + ',', 1, CHARINDEX(',', col + ','), '') AS NVARCHAR(100)) ,len(col)-len(REPLACE(col,',','')) as level
from tmp
UNION ALL
SELECT col = CAST(LEFT(Split, CHARINDEX(',', Split) - 1) AS NVARCHAR(100)),Split = CAST(STUFF(Split, 1, CHARINDEX(',', Split), '') AS NVARCHAR(100)),level-1
FROM Roy
WHERE split > '')
select col from roy where level =1
哥们儿写的好复杂,我要拼接一个SQL字符串的,这样一长串怎么去拼接啊,先看看有没有其他方法吧
如果 你的字段值 都是末尾都是 ,值, 这样的格式话,需要找出末尾的值是43 可以直接like ;传参的例子:
declare @userid varchar(20)
declare @v_sql varchar(4000)
set @userid ='43'
set @v_sql ='select * from tmp where col like ''%'+','+@userid+','+''''
exec(@v_sql)
你这个和我上面图片用的是同样的方法,但是为啥查不出来呢? userid = 2
select * from TOA_Con where c_transferid like '%,2,'
一条都没查出来
2024-10-24 07:21:26
C_TransferID的值为:,1,4,5,21,43,
我要传递一个参数进去,判断C_TransferID的最后一个数字跟传递的参数是否相等
要判断是否相等就要把C_TransferID字段中最后一个数字取出来,对吧?
我的问题就是怎么取出来,判断相等
给个简单点的方法吧 第一个哥们儿写的好复杂,我要拼接一个SQL字符串的
懂了一点,还有一点,要是是传的43相等就把最后一个数字取出来,那要是传的4了,也有相等的,那怎么办???、要明确你到底想干什么?
看一下上面的追问,看看能明白么?
就是图片中的数据类型: ,3,2, 是userid拼接成的,如果当前userid=2
那么就把以,2,结尾的调用出来,例如:
,2,3,5,6,
,4,5,2,
,4,2,5,
,2,3,4,6,2,
,3,2,
当userid=2的时候,就把
,4,5,2,
,2,3,4,6,2,
,3,2,
这3个调出来
简单了
例如:
WITH a(col) AS (
SELECT
',2,3,5,6,'
UNION ALL
SELECT ',4,5,2,'
UNION ALL
SELECT ',4,2,5,'
UNION ALL
SELECT ',2,3,4,6,2,'
UNION ALL
SELECT ',3,2,'
)
SELECT col FROM a WHERE col LIKE +'%'+'2'+'_'
所以你可以这样写 : select C_TransferID from table C_TransferID like +'%' +CAST(@userid AS NVARCHAR(40))+'_'
2024-10-24 07:39:28
给个简单点的实例呗