原帖地址是http://community.csdn.net/Expert/topic/5678/5678397.xml?temp=.177807
我的content是text字段的,如果换成varchar图片就插不进去,但是text字段的运行你给的SQL语句会有出错提示:
函数 stuff 的参数 1 的数据类型 text 无效。怎么办呢
我的content是text字段的,如果换成varchar图片就插不进去,但是text字段的运行你给的SQL语句会有出错提示:
函数 stuff 的参数 1 的数据类型 text 无效。怎么办呢
(Case When CharIndex('<img src', content) > 0 Then
Stuff(content, CharIndex('<img src', content), CharIndex('>', content, CharIndex('<img src', content) + 1) - CharIndex('<img src', content) + 1, '')
Else content End) As content From (Select id, title, Cast(content As Varchar) From 留言表 ) A
Select id, title,
(Case When CharIndex('<img src', content) > 0 Then
Stuff(content, CharIndex('<img src', content), CharIndex('>', content,
CharIndex('<img src', content) + 1) - CharIndex('<img src', content) + 1, '') Else content End) As content
From (Select id, title, Cast(content As Varchar(8000)) AS content From 留言表 ) A
如果不指定长度的话,那默认是多大呢?
Cast(content As Varchar) 转换后要不要指定列呢?
请指教...
這個沒看懂?“指定列”意思是不是指定列名?在這裡當然需要。
(Case When CharIndex('<img src', content) > 0 Then
Stuff(content, CharIndex('<img src', content), CharIndex('>', content, CharIndex('<img src', content) + 1) - CharIndex('<img src', content) + 1, '')
Else content End) As content From (Select id, title, Cast(content As Varchar) As content From 留言表 ) A
select '啊0123456789-a-0123456789-b-0123456789-c-0123456789-d'select context from Tselect
cast(context as varchar) as 默认只保留30个字节,
len(cast(context as varchar)) as 字符个数,
datalength(cast(context as varchar)) as 字节数
from Tdrop table T
(Case When CharIndex('<img src', content) > 0 Then
Substring(content, 1, CharIndex('<img src', content) - 1)
+ Substring(content, CharIndex('>', content, CharIndex('<img src', content) + 1) + 1, DataLength(content))
Else content End) As content From 留言表
Create Table 留言表
(id Int,
title Varchar(100),
content Text)
Insert 留言表 Select 1, 'A', 'ABC<img src="1.jpg" width=xx height=xx>zz'
Union All Select 2, 'B', 'dsad'
Union All Select 3, 'C', 'KKK<img src="2.jpg" width=xx height=xx>'
Union All Select 4, 'D', 'MM<img src="3.jpg" width=xx height=xx>'
Union All Select 5, 'E', 'KKK'
GO
--測試
Select id, title,
(Case When CharIndex('<img src', content) > 0 Then
Substring(content, 1, CharIndex('<img src', content) - 1)
+ Substring(content, CharIndex('>', content, CharIndex('<img src', content) + 1) + 1, DataLength(content))
Else content End) As content From 留言表
Gop
--刪除測試環境
Drop Table 留言表
--結果
/*
id title content
1 A ABCzz
2 B dsad
3 C KKK
4 D MM
5 E KKK
*/
select cast('1234567890123456789012345678901' as varchar)
---------------------------------------------------------
paoluo(一天到晚游泳的鱼)
不指定長度,有多長,就轉為多長。Cast(content As Varchar) 转换后要不要指定列呢?
<img src='/images/CustomFaceRecv/1.jpg' width='66' height='66' border=0><img src='/images/CustomFaceRecv/2.jpg' width='66' height='66' border=0><img src='/images/CustomFaceRecv/3.jpg' width='66' height='66' border=0><img src='/images/CustomFaceRecv/4.jpg' width='66' height='66' border=0>出来的时候就出错了
Select id, title,
(Case When CharIndex('<img src', content) > 0 Then
Substring(content, 1, CharIndex('<img src', content) - 1)
+ Substring(content, CharIndex('>', content, CharIndex('<img src', content) + 1) + 1, DataLength(content))
Else content End) As content From 留言表
如果是连续插入表情符号的话就会出错,比如
<img src='/images/CustomFaceRecv/1.jpg' width='66' height='66' border=0><img src='/images/CustomFaceRecv/2.jpg' width='66' height='66' border=0><img src='/images/CustomFaceRecv/3.jpg' width='66' height='66' border=0><img src='/images/CustomFaceRecv/4.jpg' width='66' height='66' border=0>出来的时候就出错了
-----------------
如果有多個的話,就需要寫個函數來處理了。
<img src='/images/CustomFaceRecv/1.jpg' width='66' height='66' border=0>,
<img src='/images/CustomFaceRecv/2.jpg' width='66' height='66' border=0>,
<img src='/images/CustomFaceRecv/3.jpg' width='66' height='66' border=0>,
<img src='/images/CustomFaceRecv/4.jpg' width='66' height='66' border=0>,
...
用函数可以处理多个,但是以上长度最多也不能大于8000