ARG! Char(185)

Question

ARG! Char(185)

Erin Ramsay

SSCrazy Eights

Points: 9728
More actions
May 17, 2011 at 1:21 pm

#240409

I'm extracting numbers from a text field and I keep getting this:
Conversion failed when converting the nvarchar value '¹' to data type int.
And yet I can't find which row contains char(185) using LIKE, CHARINDEX, PATINDEX, or anything else using either CHAR(185) or the literal character copy/pasted.
ANYONE?
I'd like to replace the CHAR(185) with a 1 or anything else for that matter.. if I could just find those particular rows.
Donalith

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChazMan SSCrazy Points: 2259 More actions · Answer 1

Couldn't you just use the ISNUMERIC() function on that field and anything with a zero won't cast to an int?

--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's one way to do it; I'm just creating a table full of high ascii chars, cross applying them, then using patindex to filter:

with mySampleData

As

(SELECT top 255 row_number() OVER (ORDER by name) as N FROM sys.columns),

MyHighAscii

AS

(SELECT CHAR(N) As TheChar FROM mySampleData WHERE N >=127)

SELECT *

FROM (SELECT 'é' As TheData UNION ALL

SELECT 'happinéss ës a warm blankët' As TheData UNION ALL

SELECT 'Other~stuff' )MyData

cross Join MyHighAscii

WHERE PATINDEX('%' + MyHighAscii.TheChar + '%',TheData) > 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 3

ChazMan (5/17/2011)
Couldn't you just use the ISNUMERIC() function on that field and anything with a zero won't cast to an int?

Unfortunately no. CHAR(185) qualifies as a numeric in the ISNUMERIC() function.

I didn't want to do it but I'll give you the query I'm running.

selectSUBSTRING(SUBSTRING(answertext,PATINDEX('%[0-9]%',answertext), LEN(answertext)),0,patindex('%[^0-9]%',SUBSTRING(answertext,PATINDEX('%[0-9]%',answertext), LEN(answertext))))

, AnswerText

, QuestionName

, StateEnum

from Table_Name_Obfuscated

where ascii(SUBSTRING(SUBSTRING(answertext,PATINDEX('%[0-9]%',answertext), LEN(answertext)),0,patindex('%[^0-9]%',SUBSTRING(answertext,PATINDEX('%[0-9]%',answertext), LEN(answertext))))) between 48 and 57

and patindex('%[0-9]%',answertext)>0

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

All fixed...thanks folks. I must have misused the PATINDEX the first time.

I updated my table with this:

update TABLE_NAME_OBFUSCATED set AnswerText = REPLACE(AnswerText, CHAR(185),1)

where PATINDEX('%' + char(185) + '%',AnswerText)>0