Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with the query please.

Author  Topic 

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-06-28 : 14:30:19
[code]
Hello,

Is there an easy way to find ASCII > 127 TSQL. I need to find all the corrupt doc files and set to 1 but I have to do manually by using Substring string below.
This can be tedious and wonder if there is a better way to write this query.

Thanks in advance.



corrupt Doc_desc
------- -----------
1 Q@ Q@ R
¥˜€R{P ÑQ‹ˆ �4gœpíI@ Q@ Q@ Q@ Q@ Q@ Q@ Q@ g4P E P E P E P (¢Š
h¢€
(¢€
(¢€
(¢€
CKE sÚŽ�"¦±."£ê £Í Ç$ (Y>^˜#'Ö§´±ÖîŠÿ l^Zù äÃk ÿ Þbz{
Ú¢€9ëÍ#]½Õ'Yuˆ×H- ·XFý¸åwv×Þº EDTQ…Q€aKE f€0(¢€
QE QE QE QE QI¹CmÈÉíšZ (¢Š (¢Š (¤Þ"ön ±œgœRÐ E P E P E P E P E P E P E P HFih QE QE QE QE QE bø´ÿ ¡[×ÔÖ" Å™û
±ôº�úÖÕo?áCçú ´ÿ �SÑ~¡E VPQE QE QE QE QE QFi²(' 72äc*pGã@ ´}:]:;…šúkÃ,í(i€á ¯×)ðýLšf­
Ï4›u)ãÞÒ Ø jŸƒôµÕô­NßPº¹ž Ô&Œ)"ƒÆ I Ÿå@ " ªYˆ rIíU4­B-RÌ]AÌlìªsÔ FJç¼&F§á+›KÖk"‚i­òìrÁO 4Ï ›{ ¦¦±|ñC," <í-Çé@ y É¢¸Øm&Õü? ÓiSÍys š· z©W# ©Ýo ŒTzÜZ¢|8"êÌðßÛF2ÑË÷°À Hë'Ö€;
�Ñ�ú ä®#ñ ¿‚Ö;Èš{¸dQ*ÄùiažGr*}
ãAÕoíî´¹ † [œ£`Žëì{Ð Êêï­6-Œ­*@f|"È :½\}žŸb>"߯-ÀÝ`' OÞ/É®ÀP �8M"Úßý®dòQ"ÉVÂ>{'íW‰ €HÉè+oÕbÙñEuy1$ n]çn@ë�Æ™­Z-ñî� y£ Á1p®pp?N´ Ö+" �#® JZäf´‡Eñ¾-,o¢Šö)Rd
Hb Aç½uÔ ×-8ñ½ÕsÓqÆiÁƒ
‚ ö®CSÔàÒ¼GvuÛGk)Ñ µÉMé *}9æ§ÔîâÑ<!4ú\Ë4rIˆ\6B‰-×Û4 ÒùÑ <¿17ÿ wpÏåNgE8f ûšåµÍ úóNXôû m¯b*Ñ\ þ`G\�2sV¼C£IªhJòí�S·ŒJ®��æ(É þé9
òê €' ÏZZç<;,>#h5¹âU- ò-,çc #ù{WG@ Q@ Q@
=4 ƒâï CáÛ2â/>àŒ`ã çÚ¼Âû^Öoï™õ=JHàq¹bˆâ0 oéZ©,¾3ÖÙ$ƒË·'FoæÎvB� ü©tk-3Qñ¤þt,º}¥‹O $ÿ
@Ü<þ
Þ ¹Ó5 gÓ®Qe¹Y>Ñ
" ¹x }Euñ4- " [Úhg
˸å�ŽïŸë^]቟NÕ`¼' 0nvo B HJõx" ޛ㜠ü¸óèy&˜‹"QE
ä¾ ´ÖɦËmwhÓÞ¤ äÌ@e öéÚºÚä>%œ[h¤ÿ ÐRë@ l1ˆcXÔ³ ˱b~¤×3ñ$:xtÜGq4O Ññ o

‚^µÔW1ñ1xFá½$‹·ûb€:?6$d‰¤@äp¥†OáOfTRÌÁTu$à
ã¼s¤YZè/"E o­ž9 嘙 Ì'Óž�+WÄ6ÐÞÛé×7-Ëmg
Ë4ѸÊÏÇ G~{sš Ù†â óäÍ ¸ë±�ÇåKçD[g˜›³Œn ®<¼IñJ-ÚÊ[4¹µ™ ²" äeG§¾
?ÄÖ'i^*Ñõô…|¹f - p"óµÿ Ÿ?J 댈 º†= <šIfŠ Ý4© ú" +8Á ïˆ<Ö�[ì1í
Gñ¿?¢ãþú}2k­Vÿ Wž]>
Á
ãZÆ²È DLt ¹Î{þ Õ£"¨d`Êz r
$ˆ$FBH c*pk
ºV¡¥O~"ùIe,že¼(弜õ Û¥oÐ/ðèÉý™¨$³I1MFt
# 8 Ç5ÑËuo
"-xÑ›€À \¯ƒg6š
"rˆY¢¿º�/© ]>ÎãWðÄFm>Úy/!.ó<Ÿ0f zq�n" Ö³*©f ( $ž G
ݼÿ êgŽO÷X å.tÝ~×ÁpZHEõÝ´¨ÎˆÇ÷Ñ+gn{ñǾ*Ɖ©hZÖ"o=²}�QWV·tØûHÁ ïƒÞ€:9®í p"O lÝ 0
8ÍåìV­-Ûò±9çq�Æ"kÚ ¥¨C °·³²¸o^)oŽ@ ¸ ( ¨y£GTy YÎ ‚~o wvÒÎðGq ÊŸy ÁeúŠå<Yh�â�
¼™IeoÒGF#8QÇó£Ä šO‰<=ue [¼·M … 7"
‡Ö€:馊 Ì"H± êÌp)×0]Gæ[Ì'§÷'� ÎMuuwãk‹Hâ†XììÑÑ%b æ<°ü0)úv‡¨Ûø¦mOý
;ˆvMo 'söoLÐ ü--ѤŽ÷ *Åþ°- '×ÒžóÂ'yÍ*,dgyaŒ}kˆðþ…§_k^'³"ƒÌ ¢ c´esŸ¯§¥\Öá[ xjÓË+¤ÆZ%\åD"p™þ™


--Testing...


-- Find all corrupt files.
SELECT --eval_text AS 'eval_text_BadValue'
check_sum
,[Path]
FROM My_pat AS a
WHERE ASCII(SUBSTRING(a.eval_text, 6, 200)) > 127 -- tedious right there....
AND [corrupt] = 0
GO


--set to 1

UPDATE b
SET [corrupt] = 1
FROM dbo.#temp AS a
JOIN My_Pat AS b
ON (a.check_sum = b.check_sum)
WHERE (b.[corrupt] = 0 );[/code]
   

- Advertisement -