August 12, 2010 at 6:15 am
Hi i am really struggling here
I have a weird character string that has all sorts of funny characters.
this has to be imported in to another CRM system
trouble is every time I import a table the new CRM system seems to change all the weird characters.
is there a function or someway i can change this 7 character ID to some thing more simple
here is an example in quotes
"TME(+░│"
August 12, 2010 at 7:35 am
Hi,
one way would be to parse the character string as a number by taking the ASCII value of the first character plus the ASCII value of the second character times 256 plus:
ASCII('T') + 256*ASCII('M') + 256*256*ASCII('E') + 256*256*256*ASCII('(') + ...
This can be done like this:
declare @data varchar(20)
set @data = 'TME(+¦¦'
select sum((256*(i-1)) + ascii(substring(@data, i, 1)))
from (select top 20 ROW_NUMBER() OVER(ORDER BY name) AS i from sys.columns) Tally
/Markus
August 12, 2010 at 10:13 am
sorry i can't get this to work
August 12, 2010 at 10:26 am
Try CASTing it as varbinary(max).
You might also want to check that the collations on the two databases match. Different collations can treat the same character differently.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2010 at 10:32 am
Thanks that seems to work (the varbinary casting)
I'd like to create a field and populate it with the varbinary value
what data type should the field be and should I cast it to something esle as well
e.g. cast (cast(field as varbinary) as varchar)
Thanks again
August 12, 2010 at 10:58 am
just realised i am being a bit of a dummy
i saved it a varbinary (max) datatype
works a treat
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply