Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identifying ASCII characters in NVARCHAR columns Expand / Collapse
Author
Message
Posted Tuesday, May 25, 2010 8:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:05 AM
Points: 24, Visits: 40
Hello,

I have an issue where I am storing various international characters in nvarchar columns, but need to branch the data at one point of processing so that ASCII characters are run through an additional cleansing process and all non-ASCII characters are set aside.

Is there a way to identify which nvarchar values are within the ASCII range and can be converted to varchar without corruption? Also, the strings may contain a mix of english and international character sets, so the entire string must be checked and not just the first character.

i.e.
Pass:
'Hello', 'abc123'

Fail:
'太平市', 'abc太123'

Any help would be appreciated and let me know if examples would make this more clear.

Regards,
Chris
Post #927503
Posted Tuesday, May 25, 2010 8:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
I know CAST will allow you to change it to a VARCHAR, and you'll end up with a variety of ? in the items for non-ASCII characters.

Perhaps you can somehow search for those placeholders and if they aren't there (CHARINDEX=0), you can do your processing?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #927526
Posted Tuesday, May 25, 2010 9:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:05 AM
Points: 24, Visits: 40
Thanks Steve, this may be an option if we cannot figure out a better way around this. I just need to confirm that we are not allowing ? characters to exist in any of the strings I am cleansing and, if so, create some type of internal process for escaping these out so they do not trigger the 'international' flag.

Appreciate the help.
Post #927578
Posted Tuesday, May 25, 2010 9:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:29 PM
Points: 12,741, Visits: 31,053
proof of concept for Steve's idea;i thought that was kind of neat:

--results:
Status cnv id val id val
------ ------------------------------ ----------- ------- ----------- -------
Pass Hello 1 Hello 1 Hello
Pass abc123 2 abc123 2 abc123
Fail ??? 3 太平市 3 太平市
Fail abc?123 4 abc太123 4 abc太123


the test code:
SELECT 1 as id,N'Hello' As val
INTo #TMP UNION ALL
SELECT 2 as id,N'abc123' UNION ALL
SELECT 3 as id,N'太平市' UNION ALL
SELECT 4 as id,N'abc太123'

SELECT
CASE
WHEN t1.val = CONVERT(varchar,t2.val)
THEN 'Pass'
ELSE 'Fail'
END As Status,
CONVERT(varchar,t2.val) As cnv,*
FROM #TMP t1
LEFT OUTER JOIN #TMP t2
ON t1.Id = t2.ID



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #927599
Posted Tuesday, May 25, 2010 10:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:05 AM
Points: 24, Visits: 40
Thanks for the code Lowell, I have been playing around with this myself and it looks like it is going to work out well for my problem.

Thanks again both of you for the help!
Post #927616
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse