Welcome Guest, you are in: Login

MUST Creative Engineering Laboratory

RSS RSS

Navigation



Technical Doc



Search the wiki
»

MUST Corp.

MUST Corp.

www.must.or.kr

 Microsoft CERTIFIED Partner Software Development, Web Development, Data Platform

 Microsoft Small Business Specialist

MCSD

Microsoft Certified IT Professional

Microsoft Certified Professional Developer
텍스트 제외 쿼리

DECLARE @T varchar(255), @C varchar(255); DECLARE Table_Cursor CURSOR FOR SELECT a.name, b.name FROM sysobjects a, syscolumns b WHERE a.id = b.id AND a.xtype = 'u' AND (b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167); OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @T, @C; WHILE (@@FETCH_STATUS = 0) BEGIN EXEC( 'Update ['+@T+'] set ['+@C+']=replace(['+@C+'], '''', '''')where ['+@C+'] like ''%''' ); FETCH NEXT FROM Table_Cursor INTO @T, @C; END; CLOSE Table_Cursor; DEALLOCATE Table_Cursor;

텍스트 포함 쿼리 - 테이블명과 칼럼명을 수동으로 집어너야됨

<nowiki>Declare @replacestr varchar(10)
Declare @deletestr varchar(100)
Declare @ptr varbinary(16)
Declare @len int
Declare @offset int
Declare @cnt int
Set @deletestr = 'http://dev-server:10001'
Set @replacestr = ''
Select @ptr = textptr(p_contents) From tb_product_list Where p_code='P000000005'
Set @len = len(@deletestr)
Set @cnt = 0

While(1=1) begin --일단 무한 loop로 돌려놓고
  Select @offset=patindex('%'+@deletestr+'%', p_contents) From tb_product_list  Where p_code='P000000005'
 If @offset = 0 Begin
  break
 End     
 Else begin
         Set @offset = @offset - 1
         Updatetext tb_product_list.p_contents @ptr @offset @len @replacestr
         Set @cnt = @cnt + 1
 End
End
Select convert(varchar(10), @cnt) + ' 개 replace'</nowiki>

8000자가 넘는지 검사하는 쿼리 0이면 업는것이고 1이면 한개가 있다는 말

DECLARE @T varchar(255), @C varchar(255); DECLARE Table_Cursor CURSOR FOR SELECT a.name, b.name FROM sysobjects a, syscolumns b WHERE a.id = b.id AND a.xtype = 'u' AND (b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167); OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @T, @C; WHILE (@@FETCH_STATUS = 0) BEGIN EXEC( 'SELECT count(*) FROM ['+@T+'] where ['+@C+'] like ''%'' AND len(convert(varchar(8000), ['+@C+']))=''8000'' ' ); FETCH NEXT FROM Table_Cursor INTO @T, @C; END; CLOSE Table_Cursor; DEALLOCATE Table_Cursor;

8000자가 안될때 텍스트 형식 으로된거 제거

DECLARE @T varchar(255), @C varchar(255); DECLARE Table_Cursor CURSOR FOR SELECT a.name, b.name FROM sysobjects a, syscolumns b WHERE a.id = b.id AND a.xtype = 'u' AND (b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167); OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @T, @C; WHILE (@@FETCH_STATUS = 0) BEGIN EXEC( 'update ['+@T+'] set ['+@C+'] = left( convert(varchar(8000), ['+@C+']), len(convert(varchar(8000), ['+@C+'])) - 6 - patindex(''%tpircs<%'', reverse(convert(varchar(8000), ['+@C+']))) ) where ['+@C+'] like ''%''' ); FETCH NEXT FROM Table_Cursor INTO @T, @C; END; CLOSE Table_Cursor; DEALLOCATE Table_Cursor;

MUST Creative Engineering Laboratory

ImageImage Image Image

Image Image Image Image Image Image Image

Copyright © 2010 MUST Corp. All rights reserved. must@must.or.kr
This Program is released under the GNU General Public License v2. View the GNU General Public License v2 or visit the GNU website.