Sometimes you may not be able to see the characters in your data in SQL Server Mangement Studio (or another application).  This simple function will allow you to pass an input string in, and it will return the individual characters as rows from a Table Valued Function.

First the code:

if object_id('[dbo].[fn_CharsToTable]') is not null
     drop function [dbo].[fn_CharsToTable]
go
create function [dbo].[fn_CharsToTable](@InputString varchar(max))
returns @Values TABLE
     (position      int not null primary key clustered
     ,theNChar      char(1)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 25-Mar-2009
*    Purpose:       splits an input string (@InputString) 
*                   into a table of single characters.
*    
*
*************************************************************/
begin

insert into @Values (position,theNChar)
select n, substring(@InputString, n,1)
from    dbo.vw_Nums
where   n <= datalength(@InputString)
order by n
               
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_CharsToTable]('a|%25basdf|%25c|%25d')
select theValue from [dbo].[fn_CharsToTable]('a')
select * from [dbo].[fn_CharsToTable]('a basdf c d')

select * from [dbo].[fn_CharsToTable]('a|%25b
asdf|%25c|%25d')*/
GO

Using the function is simple.  You simply select from it like any other Table Valued Function.

select position,theNChar,ascii(thenChar) as 'AsciiValue' from dbo.fn_charstotable('hello, my name is Brandon')

The result will be the position of the character within the string, the character itself (if it's not printable, you won't see it), and the ASCII value of the character.

The below is a unicode version of the same code:

if object_id('[dbo].[fn_CharsToTable]') is not null
     drop function [dbo].[fn_CharsToTable]
go
create function [dbo].[fn_CharsToTable](@InputString nvarchar(max))
returns @Values TABLE
     (position      int not null primary key clustered
     ,theNChar      nchar(1)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 25-Mar-2009
*    Purpose:       splits an input string (@InputString) 
*                   into a table of single characters.
*    
*
*************************************************************/
begin

insert into @Values (position,theNChar)
select n, substring(@InputString, n,1)
from    dbo.vw_Nums
where   n <= datalength(@InputString)/2
order by n
               
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_CharsToTable]('a%25basdf%25c%25d')
select theValue from [dbo].[fn_CharsToTable]('a')
select * from [dbo].[fn_CharsToTable]('a basdf c d')

select * from [dbo].[fn_CharsToTable]('a%25b
asdf25c%25d')*/
GO

The usage of the function is the same, except instead of the ASCII() function, you would use the UNICODE() function.

select position,theNChar,unicode(thenChar) as 'AsciiValue' from dbo.fn_charstotable('hello, my name is Brandon')