|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 42,
Visits: 279
|
|
Can any one tell me the sql query to find the ASCII characters (0 to 127) from multiple columns in a table.
Thanks.
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 8:39 PM
Points: 11,638,
Visits: 27,713
|
|
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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 42,
Visits: 279
|
|
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.
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906,
Visits: 26,792
|
|
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."
For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 42,
Visits: 279
|
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 8:39 PM
Points: 11,638,
Visits: 27,713
|
|
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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 42,
Visits: 279
|
|
Yes, am looking for the special charaacters in the column other than numbers and the alphabets.
Thanks.
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 5,102,
Visits: 20,205
|
|
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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 42,
Visits: 279
|
|
I have this type of ASCii characters in the column how do i find all in a single query other than alphanumarics.
Thanks, (space) ! " # $ % & ' ( ) * + , - . / : ; < = > ?
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 8:39 PM
Points: 11,638,
Visits: 27,713
|
|
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
|
|
|
|