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 ««1234»»»

how to find ASCII characters in a table? Expand / Collapse
Author
Message
Posted Friday, July 29, 2011 11:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:50 AM
Points: 477, Visits: 1,053
Hey Anuganti,

See if I understand you correctly the belwo would fit your requirement

CREATE FUNCTION dbo.GetAsciiValue
(
@str varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @res varchar(1000)

SELECT @res=COALESCE(@res,'')+CAST(ASCII(SUBSTRING(@str,number,1)) AS varchar(5))
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND LEN(@str)

RETURN @res
END

then use it like

select name,number,dbo.GetAsciiValue(number) AS AsciiEqv
from yourtable





--SQLFRNDZ
Post #1151161
Posted Friday, July 29, 2011 6:08 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
ranuganti (7/29/2011)
I have this type of ASCii characters in the column how do i find all in a single query other than alphanumarics.


Let's peel one potato at a time...

Based on what you said and what emphasized in the quote above, are you asking to be able to find data that has anything other than A to Z, a to z, and 0 to 9 in it?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1151370
Posted Friday, July 29, 2011 6:09 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
Heh... also... why on Earth does it have to be a "single query"? You doing something for a contest or what?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1151371
Posted Saturday, July 30, 2011 8:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
If you need to know which characters have been found / or if found removed from the input string try this:

 DECLARE @Temp VARCHAR(30)
DECLARE @Found VARCHAR(30)
SET @Found =''
SET @Temp = 'A<>B=1,! \|-*()%$#@!'
SELECT @Temp AS 'Original input'
BEGIN
WHILE PATINDEX('%[^a-z^0-9]%', @Temp) > 0
BEGIN
SET @Found = @Found + (SELECT SUBSTRING(@Temp,PATINDEX('%[^a-z^0-9]%', @Temp),1))
SET @Temp = STUFF(@Temp, PATINDEX('%[^a-z^0-9]%', @Temp), 1, '')
END
SELECT @Found AS 'Found'
SELECT @Temp AS 'With characters removed'
END
Results:
Found
<>=,! \|-*()%$#@!
With characters removed
AB1

Note that the above will NOT find or remove the '^' character.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1151431
Posted Thursday, November 1, 2012 7:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 12:26 PM
Points: 5, Visits: 17
I have character or symbol in my column like "□". Please help me to find out the rows which are all having this("□") character.

My column value: Digital □ Packet Data

I have tried the following query :

select * from tbl_example where PATINDEX('%[□]%',field_name) > 0

I got results with "?" character. Please help me to find "□" character in the fields.

Thanks!!!
Post #1379791
Posted Thursday, November 1, 2012 7:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
mkarthikeyyan 89837 (11/1/2012)
I have character or symbol in my column like "□". Please help me to find out the rows which are all having this("□") character.

My column value: Digital □ Packet Data

I have tried the following query :

select * from tbl_example where PATINDEX('%[□]%',field_name) > 0

I got results with "?" character. Please help me to find "□" character in the fields.

Thanks!!!


I put:

SELECT '□' FIELD

into SQL and it returned "?". So it may be that your query actually works, but that SQL will display the box symbol as a question mark in query results.
Post #1379797
Posted Thursday, November 1, 2012 7:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 12:26 PM
Points: 5, Visits: 17
Please help me to find unpredictable special characters like № , ffl in fields.

For example:

declare @tbl table(val nvarchar(100) null)

insert into @tbl
select 'ffl'
union all
select '№'
union all
select '©'


select * From @tbl


It returns "?" only. Please help me how to display these special characters?

Thanks!!!
Post #1379824
Posted Thursday, November 1, 2012 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 12,962, Visits: 32,503
mkarthikeyyan 89837 (11/1/2012)
Please help me to find unpredictable special characters like № , ffl in fields.

For example:
It returns "?" only. Please help me how to display these special characters?

Thanks!!!

because you implicitly converted nvarchar to varchar;

once converted to varchar's best guess, it cannot be undone,s o you get the boxy/question marks.
if you explicitly declare the strings with N'String' it works fine:

declare @tbl table(val nvarchar(100) null)

insert into @tbl
select N'ffl'
union all
select N'№'
union all
select N'©'

select * from @tbl



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 #1379831
Posted Thursday, November 1, 2012 8:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:22 AM
Points: 167, Visits: 691
As indicated in a previous reply, the '?' may be the right answer - remember that the SSMS query results panel is not a text editor, and it may be limited in displaying special characters.

Apart from the other suggestions, you could write a SP that would look at all the columns you are interested and go htrough each character (RBAR-max - i.e. row-by-row, column-by-column-character-by-character ) and determine which ones you don't like.
You could also use regular expressions - which would be much better and faster.

I don't have access to SQLServer right now - working on Oracle today, in which case I would use something like:
-- match only alphanumeric characters (a-z, A-Z, and 0-9)
SELECT *
FROM regex_test
WHERE REGEXP_LIKE(regex_col, '[[:alnum:]]');

untested, but I think you can do something like
SELECT *
FROM yourtable
WHERE yourcolumn NOT LIKE '[a-z]|[A-Z]|[0-9]'

B
Post #1379838
Posted Thursday, November 1, 2012 8:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
Here's a good page for some nice hints on dealing with unicode:

http://msdn.microsoft.com/en-us/library/ms180059.aspx
Post #1379839
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse