May 20, 2008 at 9:59 am
OK so I've built myself a nice ASCII table in SQL with the ASCII Code, the character it represents and then a 1 or a 0 in a column depending on whether I want a string to contain the character or not.
SO, tblASCII looks like this:
ASCIICode | ASCIChar | NameChar
64 @ 0
91 [ 0
92 \ 0
I'm sure you get the idea...
Now, I am using PATINDEX to detect undesirable characters in a string, something like:
select
contact_id,
contact_firstname,
PATINDEX ( '%[!"£$%^&*]%' , contact_firstname) as Incorrectchars
FROM tblcontact
order by Incorrectchars desc
This works fine, however, I'd like the pat index [] characters to be taken from the tblASCII when NameChar = 0.
As I understand it, PATINDEX needs a list of values, and can't be obtained using a select statement.
I tried to use Excel and concatenate together a list of the characters I wanted (yes, all 131! lol), however, the characters did not paste back in properly, and many of them were simply square boxes.
'%[ !"#$%&()*+,/0123456789:; ?@[\]^_{|}~€ƒ„…†“”•˜™š›œ £¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹ºß÷]%
Is what I have in Excel, and its fine in Notepad, but when I put it in to SQL I get loads of random squares instead of characters for about 15 of the characters.
Any ideas how I can avoid this?!
May 20, 2008 at 10:06 am
Have you tried to run the query? Just because it can't DISPLAY the characters correctly doesn't mean it won't FIND them. You may find that your PATINDEX runs exactly as you want it to (and picks up those non-printing characters you're trying to get rid of).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 20, 2008 at 10:53 am
I have to ask if you really need to use PatIndex for this. Do you? Wouldn't a join with Like do what you need?
select
contact_id,
contact_firstname, count(*) as IncorrectChars
FROM tblcontact
inner join tblASCII
on contact_firstname like '%' + ASCIIChar + '%'
and NameChar = 0
group by contact_id, contact_firstname
order by Incorrectchars desc
Would something like that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 1:57 am
I completely had not thought of doing it like that GSquared, it's a much tidier and simpler way of doing it that works just fine! - I tend to find that once I know of a new technique / function etc I end up using it all over the shop - I get kinda blinkered towards it!! (This was the case with PATINDEX, something I was made aware about a week ago!)
Thanks 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply