[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1263
  • Last Modified:

SQL Script to find spaces in a string using Oracle SQL Developer.

I need a SQL script using Oracle SQL Developer to find any spaces in a string.
0
donnie91910
Asked:
donnie91910
  • 8
  • 5
2 Solutions
 
slightwv (䄆 Netminder) Commented:
And do what with them?  Count them, replace them, what?
0
 
donnie91910Author Commented:
Basically just identify them if they exist in a field in a table.
0
 
donnie91910Author Commented:
I need to check if there are spaces at the beginning, at the end or in the middle of the string.
Thanks.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
slightwv (䄆 Netminder) Commented:
>>Basically just identify them if they exist in a field in a table.

select instr(column_name,' ') from table_name;

>>I need to check if there are spaces at the beginning, at the end or in the middle of the string.

Do you mean you need to return: 'Beginning','Middle','End'?

What it there are spaces in all 3?

Please post sample data and expected results.
0
 
donnie91910Author Commented:
The space is between the the 2 and the -, which is in the middle of the string.  
ZF95182 -555

The SQL script would find this entry in the field.  

I only need to find any entries with a space in the middle of the string.
0
 
slightwv (䄆 Netminder) Commented:
See if this works for you.

If not please add to the test case and explain why it doesn't work:

--set up test
drop table tab1 purge;
create table tab1(col1 varchar2(20));

insert into tab1 values(' beginning_space');
insert into tab1 values('ending_space ');
insert into tab1 values('in the middle');
insert into tab1 values(' all three ');
commit;

--actual select to loate the spaces in the middle
select col1 from tab1 where regexp_instr(col1,'\S\s\S') > 0;

Open in new window

0
 
awking00Commented:
select col1 from tab1 where instr(trim(col1),' ') > 0;
0
 
slightwv (䄆 Netminder) Commented:
Better!
0
 
donnie91910Author Commented:
How would I do spaces at the beginning of the string.
0
 
slightwv (䄆 Netminder) Commented:
>>How would I do spaces at the beginning of the string.

Please clairfy your requirements.  Above you stated:  "I only need to find any entries with a space in the middle of the string."

Based on sample test case above, what are your expected results.
0
 
donnie91910Author Commented:
How would I find if there were a space or spaces at the beginning of the string? Thanks.
0
 
slightwv (䄆 Netminder) Commented:
>>How would I find if there were a space or spaces at the beginning of the string?

Not knowing your expected results, the quickest using the test case above:

select * from tab1 where length(col1) != length(ltrim(col1,' '));

For the end:  replace ltrim with rtrim.
0
 
slightwv (䄆 Netminder) Commented:
Guess you don't even need length:

select * from tab1 where col1 != ltrim(col1,' ');
0
 
slightwv (䄆 Netminder) Commented:
I was already playing around with this when you accepted it so I figured I would go ahead and post it just in case it can head off a new question.

There are easier ways to get some of these like 'All spaces' can be a simple "trim(col1) is null" but I wanted to stick to the regexp theme that I believe you will need for others.

Here is every possible combination I can think of:
drop table tab1 purge;
create table tab1(col1 varchar2(25));

insert into tab1 values(' beginning_space');
insert into tab1 values(' beginning and middle');
insert into tab1 values('ending_space ');
insert into tab1 values('ending and middle ');
insert into tab1 values('in the middle');
insert into tab1 values(' all three ');
insert into tab1 values('no_spaces');
insert into tab1 values(' ');
commit;

select col1,
case 
	when regexp_instr(col1,'^[ ]+$') > 0 then 'All spaces'
	when regexp_instr(col1,'^[^ ]+$') > 0 then 'No spaces'
	when regexp_instr(col1,'^[ ]+[^ ]+$') > 0 then 'Only leading spaces'
	when regexp_instr(col1,'^[^ ]*[ ]+$') > 0 then 'Only trailing spaces'
	when regexp_instr(col1,'^[^ ]+.*[ ]+.*[^ ]$') > 0 then 'Only middle spaces'
	when regexp_instr(col1,'^[ ]+.*[^ ]+[ ]+[^ ]+$') > 0 then 'Leading and middle spaces'
	when regexp_instr(col1,'^[^ ]+.*[ ]+[^ ]+[ ]+$') > 0 then 'Trailing and middle spaces'
	when regexp_instr(col1,'^[ ]+.*[ ]+[^ ]+[ ].*+$') > 0 then 'All three'
end
from tab1;

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now