SQL Queries HELP! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Queries HELP!

Author

Topic

eplam2
Starting Member

11 Posts

Posted - 2013-09-02 : 06:09:54

Say I have a lists of names: Mark Chen, Peter Hi, Michael O'Donnell under a column lists called 'NAMES'.

Upon loading the data, Oracle Developer made some few encoding errors and as a consequence, it substituted an unrecognized characters with an underscore character. Say I have a long lists of names, does anyone know how can I use SQL queries to find the names that contains an underscore? (ie. Michael O_Donnell)

Thanks in Advance!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-09-02 : 08:00:47

can you try this?
select column_name from table_name where column_name like '%[_]%'

mohammad.javeed.ahmed@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 08:31:20

also see

http://visakhm.blogspot.in/2013/01/wildcard-character-based-pattern-search.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

eplam2
Starting Member

11 Posts

Posted - 2013-09-02 : 08:37:30

I've tried both that and '%_%'

but it still doesn't work....

'%_%' will return the whole table whereas
'%[_]%' will give me no row selected

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 08:56:00

>> '%[_]%' will give me no row selected
means you have NO records which contains underscore in the column_name data

--Alternate is:
WHERE column_name like '%@_%' ESCAPE '@'
--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 09:01:22

try taking the ASCII value and see if its really an _ character

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

eplam2
Starting Member

11 Posts

Posted - 2013-09-02 : 09:05:52

Yep! It worked, Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 09:13:38

quote:
Originally posted by eplam2

Yep! It worked, Thank you!

so what was the issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

eplam2
Starting Member

11 Posts

Posted - 2013-09-02 : 09:18:11

I dont know to be honest.....It worked using this '%@_%' ESCAPE '@' and returned names with an undercore, but it doesnt work when I used '%[_]%' or '%_%'

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 09:31:20

quote:
Originally posted by eplam2

Yep! It worked, Thank you!

Welcome

Any way '%[_]%' method should also work...
Which version of SQL Server you have?

--
Chandu

eplam2
Starting Member

11 Posts

Posted - 2013-09-02 : 18:53:08

Im using Oracle SQL Developer

I think the syntax might be different from other servers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-03 : 09:16:04

quote:
Originally posted by eplam2

Im using Oracle SQL Developer

I think the syntax might be different from other servers

then you should have posted this in Oracle forum
This is ms sql server forum and we deal with t-sql here. there're not many people with expertise on oracle here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources