July 29, 2011 at 9:20 am
Can any one tell me the sql query to find the ASCII characters (0 to 127) from multiple columns in a table.
Thanks.
July 29, 2011 at 9:24 am
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
July 29, 2011 at 9:37 am
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.
July 29, 2011 at 9:43 am
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
Change is inevitable... Change for the better is not.
July 29, 2011 at 9:46 am
Yes
July 29, 2011 at 9:46 am
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
July 29, 2011 at 9:47 am
Yes, am looking for the special charaacters in the column other than numbers and the alphabets.
Thanks.
July 29, 2011 at 11:05 am
Is this what you desire to do?
CREATE TABLE #T(Id INT,SomeText VARCHAR(300))
INSERT INTO #T
SELECT 9,'This is 1Tab' UNION ALL
SELECT 13, 'This is a
line feed' UNION ALL
SELECT 1000,'This has a taband 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 1Tab
13 This is a
line feed
1000 This has a taband a line
feed
Edited to correct tab, carriage return and line feed useage
July 29, 2011 at 11:22 am
I have this type of ASCii characters in the column how do i find all in a single query other than alphanumarics.
Thanks,
(space)
!
"
#
$
%
&
'
(
)
*
+
,
-
.
/
:
;
<
=
>
?
July 29, 2011 at 11:29 am
here's one way:
select * from YOURTABLE where PATINDEX('%[ !"#$%&''()*+,-./:;<=>?]%',YOURCOLUMN) > 0
--or
select * from YOURTABLE where YOURCOLUMN LIKE '%[ !"#$%&''()*+,-./:;<=>?]%'
Lowell
July 29, 2011 at 11:43 am
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
July 29, 2011 at 6:08 pm
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 [font="Arial Black"]alphanumarics[/font].
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
Change is inevitable... Change for the better is not.
July 29, 2011 at 6:09 pm
Heh... also... why on Earth does it have to be a "single query"? You doing something for a contest or what? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2011 at 8:37 am
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.
November 1, 2012 at 7:07 am
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!!!
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply