?
Solved

sql query with string operation

Posted on 2009-02-18
10
Medium Priority
?
673 Views
Last Modified: 2013-12-07
i want to write sql query with string operation...

In filed Got First name then space last name.....
i want to select only first name and differenciate by space.
In sql only
Thanks
0
Comment
Question by:ramChand123
  • 5
  • 4
10 Comments
 
LVL 21

Expert Comment

by:flow01
ID: 23676051
select substr(name,1,instr(name,' ') - 1) first_name from table

what should happen if there is no space ?

select decode instr(name,' '), 0,'No firstname',
                                                   substr(name,1,instr(name,' ') - 1) )  first_name from table
0
 

Author Comment

by:ramChand123
ID: 23676105
in one filed i have

"abc def"

i want only abc only.......
0
 

Author Comment

by:ramChand123
ID: 23676110
i m using firebird
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:ramChand123
ID: 23676223
in firebird instr and substr is not working
0
 
LVL 21

Expert Comment

by:flow01
ID: 23676230
I don't know of firebird , can you execute the shown sql ? (modified for your table)
0
 

Author Comment

by:ramChand123
ID: 23676359
any firebird expert ?
0
 
LVL 21

Expert Comment

by:flow01
ID: 23676420
substr infirebird =
SUBSTRING(<str> FROM startpos [FOR length])

an equivalent of instr I can't find fast but maybe you know a function firebird to determine the position of a character in a string  (thats what instr does)
0
 
LVL 25

Expert Comment

by:reb73
ID: 23676698
Try -

SELECT SUBSTRING(name,STRPOS(' ')-1) FROM table
0
 

Author Comment

by:ramChand123
ID: 23678102
sorry but strpos is not working.....
0
 
LVL 21

Accepted Solution

by:
flow01 earned 1500 total points
ID: 23684507
Is this of any help ?
http://www.volny.cz/iprenosil/interbase/ip_ib_code_string.htm#_code_strpos
Position of substring function in SP

create function POS in the example
SELECT SUBSTRING(name,POS(' ')-1) FROM table
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question