Removing spaces between two words in SQL

Kpatel
pts.
Tags:
DataCenter
MySQL
Oracle
SQL Server
Hi, I have a table called employee and column called Name. The column Name contains (firstname lastname)i.e. firstname 'space' lastname. I want to retrieve just the firstname from the column Name for ALL the employees. I have tried using the function "substring" but since the firstname can be of VARIABLE length, I do not know what to specify in the argument length of the substring function. Is there any function similar to substring(Name,1,' ') ? Can you suggest any solution? Thanks, K
Asked: Last updated:
Related Questions

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can use the following formula:

substring( Name, ( charindex( ‘ ‘, Name ) + 1 ), 50 )

This will find the position of the space character and add one to it to get the first letter of the second word. The parameter, 50, can be increased if any of your surnames are longer than 50 characters.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Jhoman
    You don't specify which database you are using, but in MS SQL Server the function CHARINDEX will let you find the first occurance of a space - then use that number - 1 for the length of the LEFT expression on your name column. You can find these functions in the SQL Server Books Online help.
    0 pointsBadges:
    report
  • Dansken
    If you are on an Oracle platform you would use an instr to find the space. substr(Name,1,instr(Name,' '))
    0 pointsBadges:
    report
  • Randym
    For removing the space, Oracle has the REPLACE function: SQL> select replace('this is an example',' ',' ') from dual; REPLACE('THISISANE ------------------ this is an example
    1,740 pointsBadges:
    report
  • Olgeni
    for ORACLE you can use select substr(name,1,instr(name,' ')-1) from employees; Ex// select substr('Smith Mary',1,instr('Smith Mary',' ')-1) last_name from dual; last_name --------- Smith
    0 pointsBadges:
    report
  • Maverick2000
    If you're on Oracle Platform then you can try this: select substr('Name',0, INSTR('Name',' ')) "First Name" FROM Names_Table
    0 pointsBadges:
    report
  • Jhakas
    I Guess It would be Select (Substr('FirstName LastNAme',1, (instr('FirstName LastNAme',' ')-1))) from dual; If the DB is Oracle.
    0 pointsBadges:
    report
  • Coolvinnu2005
    Hi below is the solution for ur problem. Hope by this time you might have got a best answer. If not then you can use the below query select substr('FirstName LastName',1,(instr('FirstName LastName',' ')-1)) from dual
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: