how to find ASCII characters in a table?

Question

how to find ASCII characters in a table?

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

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.

mkarthikeyyan 89837 Old Hand Points: 313 More actions · Answer 2

Please help me to find unpredictable special characters like ? , ? in fields.

For example:

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

insert into @tbl

select '?'

union all

select '?'

union all

select '©'

select * From @tbl

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

Thanks!!!

Lowell SSC Guru Points: 323518 More actions · Answer 3

mkarthikeyyan 89837 (11/1/2012)
Please help me to find unpredictable special characters like ? , ? 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'?'

union all

select N'?'

union all

select N'©'

select * from @tbl

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bleroy Hall of Fame Points: 3052 More actions · Answer 4

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

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 5

Here's a good page for some nice hints on dealing with unicode:

http://msdn.microsoft.com/en-us/library/ms180059.aspx

mkarthikeyyan 89837 Old Hand Points: 313 More actions · Answer 6

mkarthikeyyan 89837

Old Hand

Points: 313

November 1, 2012 at 10:38 pm

#1555281

Thanks a lot for your reply. Its working fine.

mkarthikeyyan 89837 Old Hand Points: 313 More actions · Answer 7

I need to find out the rows which are all having unpredictable characters only.

Example:

computers data ? exist

mobile ? communication

technology ? review

Is there any way to find rows having symbols like ?, ?,?, etc (Unpredictable special characters) only, not like /,%,@ and all .

Thanks!!!

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 8

mkarthikeyyan 89837 (11/2/2012)
I need to find out the rows which are all having unpredictable characters only.
Example:
computers data ? exist
mobile ? communication
technology ? review
Is there any way to find rows having symbols like ?, ?,?, etc (Unpredictable special characters) only, not like /,%,@ and all .
Thanks!!!

This will do it for you. Its simplicity is based solely on some old school knowledge of the ASCII value table and how collations affect character comparisons. Because the characters that you're using are "unicode" characters, you MUST use the "N" prefix (as Lowell stated) for any and all string costants. See the embedded comments.

--===== Conditionally drop the test table to make

-- reruns in SSMS easier.

-- This is not a part of the solution

IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

--===== Create and populate the test table.

-- This is not a part of the solution

CREATE TABLE #MyHead (RowNum INT, SomeString NVARCHAR(50))

INSERT INTO #MyHead (RowNum, SomeString)

SELECT 1,N'computers data ? exist' UNION ALL

SELECT 2,N'mobile ? communication' UNION ALL

SELECT 3,N'good row' UNION ALL

SELECT 4,N'so is (this)!?@#$%^&*-_=+[{]}\|;:",<.>/?' UNION ALL

SELECT 5,N'technology ? review'

;

--===== Display the contents of the table

SELECT * FROM #MyHead

--===== This returns all the bad rows only.

-- Rows 3 and 4 are missing because they're good rows.

-- The LIKE filter here includes anything that is NOT

-- between a SPACE (CHAR(32)) and a TILDE (CHAR(126))

-- which are normally considered to be "normal" or

-- "predictable" characters.

SELECT *

FROM #MyHead

WHERE SomeString LIKE N'%[^ -~]%' COLLATE Latin1_General_BIN

;

Here are the result sets. The first is simply the contents of the table. The second is only the "bad" rows from the table.

(5 row(s) affected)

RowNum SomeString

----------- --------------------------------------------------

1 computers data ? exist

2 mobile ? communication

3 good row

4 so is (this)!?@#$%^&*-_=+[{]}\|;:",<.>/?

5 technology ? review

(5 row(s) affected)

RowNum SomeString

----------- --------------------------------------------------

1 computers data ? exist

2 mobile ? communication

5 technology ? review

(3 row(s) affected)

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 9

bleroy (11/1/2012)
You could also use regular expressions - which would be much better and faster.

Oh, be careful, now. In T-SQL, that's not necessarly true. In fact, in most "simple" cases, like for this problem, it's usually not true. Please see the discussion and testing that was done within that disucussion at the following URL. It's a bit long winded but definitely worth studying.

http://www.sqlservercentral.com/Forums/Topic1296195-60-1.aspx

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bleroy Hall of Fame Points: 3052 More actions · Answer 10

Jeff Moden (11/2/2012)
bleroy (11/1/2012)
You could also use regular expressions - which would be much better and faster.
Oh, be careful, now. In T-SQL, that's not necessarly true. In fact, in most "simple" cases, like for this problem, it's usually not true. Please see the discussion and testing that was done within that disucussion at the following URL. It's a bit long winded but definitely worth studying.
http://www.sqlservercentral.com/Forums/Topic1296195-60-1.aspx

Read article and then read discussion .... very interesting indeed! Some excellent points to keep in mind when deciding which approach to use, thanks for the pointer 🙂

B

mkarthikeyyan 89837 Old Hand Points: 313 More actions · Answer 11

Hi,

Please help to solve this !!!

i need to create tables and insert values in following tables, the scenario is

create table tbl1(tbl1_col1 int primary key ,tbl1_col2 int)

create table tbl2(tbl2_col1 int ,tbl2_col2 int primary key)

Is it possible to add reference(foreign key) tbl1_col1 to tbl2_col1 and

add reference(foreign key) tbl2_col2 to tbl1_col2 ?

If so how how can i create tables with this criteria and insert values in single statement?

Thanks in advance !!!

Lowell SSC Guru Points: 323518 More actions · Answer 12

mkarthikeyyan 89837 (12/17/2012)
Hi,
Please help to solve this !!!
i need to create tables and insert values in following tables, the scenario is
create table tbl1(tbl1_col1 int primary key ,tbl1_col2 int)
create table tbl2(tbl2_col1 int ,tbl2_col2 int primary key)
Is it possible to add reference(foreign key) tbl1_col1 to tbl2_col1 and
add reference(foreign key) tbl2_col2 to tbl1_col2 ?
If so how how can i create tables with this criteria and insert values in single statement?
Thanks in advance !!!

wow that table design is so rough to read....

in your attempt to turn the tables into examples you made it harder to read and understand.

things i would change:

1. tbl1 and tbl2 are not very descriptive...use real names or concept names. Header/Detail or Library/Books or something would help

2. for me, the primary key of a table is ALWAYS the first column in a table. i'd change the column order for your table definition.

a foreign key is easy to add.

here's your sql modified, as is, for example:

create table tbl2(tbl2_col1 int ,tbl2_col2 int primary key)

create table tbl1(tbl1_col1 int primary key ,tbl1_col2 int REFERENCES tbl2(tbl2_col2))

--or after the table is created

ALTER TABLE tbl1 ADD CONSTRAINT FK_tbl1 FOREIGN KEY (tbl1_col2) REFERENCES tbl2(tbl2_col2)

for insert int0 the data, two tables require two insert statements...but you might be able to use the OUTPUT clause to do them together.

it depends on the data you have that you want to insert.

a nice basic example of the OUTPUT:

CREATE TABLE adds(

adid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

code VARCHAR(30) )

DECLARE @MyResults TABLE(

ID int,

newcode VARCHAR(30),

oldcode VARCHAR(30) )

INSERT INTO adds(code)

OUTPUT

INSERTED.adid,

INSERTED.code,

NULL

INTO @MyResults

SELECT 'aliceblue' UNION ALL SELECT 'antiquewhite' UNION ALL

SELECT 'aqua*' UNION ALL SELECT 'aqua*' UNION ALL

SELECT 'aquamarine' UNION ALL SELECT 'azure' UNION ALL

SELECT 'beige' UNION ALL SELECT 'bisque' UNION ALL

SELECT 'black*' UNION ALL SELECT 'black*' UNION ALL

SELECT 'blanchedalmond' UNION ALL SELECT 'blue*' UNION ALL

SELECT 'blue*' UNION ALL SELECT 'blueviolet' UNION ALL

SELECT 'brown' UNION ALL SELECT 'burlywood' UNION ALL

SELECT 'cadetblue'

UPDATE dbo.adds

SET code = UPPER(SUBSTRING(code, 1, LEN(code) - 3) )

OUTPUT

INSERTED.adid,

INSERTED.code,

DELETED.code

INTO @MyResults

WHERE LEFT(code,1) = 'a'

SELECT * FROM @MyResults

/*

--results of update

ID newcode oldcode

1 ALICEB aliceblue

2 ANTIQUEWH antiquewhite

3 AQ aqua*

4 AQ aqua*

5 AQUAMAR aquamarine

6 AZ azure

*/

DROP TABLE adds

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 13

mkarthikeyyan 89837 (12/17/2012)
If so how how can i create tables with this criteria and insert values in single statement?
Thanks in advance !!!

Read up on the subject of "Instead-of Triggers" to pull this type of thing off in a "single" statement.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 14

mkarthikeyyan 89837 (12/17/2012)
Hi,
Please help to solve this !!!
i need to create tables and insert values in following tables, the scenario is
create table tbl1(tbl1_col1 int primary key ,tbl1_col2 int)
create table tbl2(tbl2_col1 int ,tbl2_col2 int primary key)
Is it possible to add reference(foreign key) tbl1_col1 to tbl2_col1 and
add reference(foreign key) tbl2_col2 to tbl1_col2 ?
If so how how can i create tables with this criteria and insert values in single statement?
Thanks in advance !!!

Actually, this thread belongs to someone else on a totally different subject. They call this "thread hijacking".

Please open a new thread of your own when you want to change subjects from the original thread.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nathanevans5+junk Valued Member Points: 64 More actions · Answer 15

This query will test for a range of ASCII characters. The below returns any with a 3 or 4, but you can adjust the char to get your results. If the characters you are looking for aren't all sequential you would need to put an additional 'AND' condition in.

SELECT T.Column

FROM TABLE T

WHERE T.Column LIKE '%[' + CHAR(51) + '-' + CHAR(52) + ']%'