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 9:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 44, Visits: 323
Can any one tell me the sql query to find the ASCII characters (0 to 127) from multiple columns in a table.

Thanks.
Post #1151028
Posted Friday, July 29, 2011 9:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
failrly easy to generate your own table of ascii chars, but i don't understand what you meant by from mulitple columns;

can you explain what you were after?
here's one example:

with Tally (N)
AS
( select top 127 row_number() over (Order By Name) from sys.columns
)
select N-1 as CHARVALUE,CHAR(N-1) as ASCIICHAR
from Tally



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 #1151032
Posted Friday, July 29, 2011 9:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 44, Visits: 323
I have a table in which a couple of columns (varchar) is having ASCii characters so how do i find them using a sql query?

Thanks.
Post #1151043
Posted Friday, July 29, 2011 9:43 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
ranuganti (7/29/2011)
I have a table in which a couple of columns (varchar) is having ASCii characters so how do i find them using a sql query?

Thanks.


ALL characters in a varchar column are ASCII characters. Which characters are you looking for? Just "control" characters such as TABs, CARRIAGE RETURNs, LINEFEEDs, etc?


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1151049
Posted Friday, July 29, 2011 9:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 44, Visits: 323
Yes
Post #1151051
Posted Friday, July 29, 2011 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
ranuganti (7/29/2011)
I have a table in which a couple of columns (varchar) is having ASCii characters so how do i find them using a sql query?

Thanks.


you are still not clear. if you have two rows of data in the varchar table you are talking about, say that had the values "MIAMI" and "DALLAS", what would be the expected results?


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 #1151052
Posted Friday, July 29, 2011 9:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 44, Visits: 323
Yes, am looking for the special charaacters in the column other than numbers and the alphabets.

Thanks.
Post #1151053
Posted Friday, July 29, 2011 11:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 5,469, Visits: 23,464
Is this what you desire to do?
CREATE TABLE #T(Id INT,SomeText VARCHAR(300))
INSERT INTO #T
SELECT 9,'This is 1 Tab' UNION ALL
SELECT 13, 'This is a
line feed' UNION ALL
SELECT 1000,'This has a tab and a line
feed' --edited ,, us a tab, carriage return and a line feed
----single tab character in between % in first LIKE and a carriage return and Line feed in second LIKE
SELECT ID FROM #T WHERE SomeText LIKE '% %' OR SomeText LIKE '%
%'

Displaying the results as text using SSMS
Id          SomeText
----------- -------------------
9 This is 1 Tab
13 This is a
line feed
1000 This has a tab and a line
feed

Edited to correct tab, carriage return and line feed useage


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 #1151116
Posted Friday, July 29, 2011 11:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 44, Visits: 323
I have this type of ASCii characters in the column how do i find all in a single query other than alphanumarics.

Thanks,
(space)
!
"
#
$
%
&
'
(
)
*
+
,
-
.
/
:
;
<
=
>
?


Post #1151138
Posted Friday, July 29, 2011 11:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
here's one way:
select * from YOURTABLE where PATINDEX('%[ !"#$%&''()*+,-./:;<=>?]%',YOURCOLUMN) > 0
--or
select * from YOURTABLE where YOURCOLUMN LIKE '%[ !"#$%&''()*+,-./:;<=>?]%'



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 #1151144
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse