sql query spaces

hi all i have my data like this
MP1M A
QX31 S
MP1M A
FA20 S
FA20 S
PTTF      P        F
FN3NM0014 P        S
FN3NM0014 P        S
FN3NM0014 S        S
FN3NM0014 S        S
FN3NM0014 S        S
FN3NM0014 S        S

i want to remove all the data after the first space. i dont want anything after the first space.  how can i write a query the filed name for this "billcode"  

romeiovasuAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT LEFT(urColumn, CHARINDEX(' ', urColumn)-1 )
0
 
romeiovasuAuthor Commented:
aneesh how do you do this in access
0
 
digital_thoughtsCommented:
All you need to do is find the index (location) of the first space and if it does exist, then do a LEFT on that length:

SELECT
      LEFT(billcode, CASE WHEN CHARINDEX(' ', billcode) = 0 THEN LEN(billcode) ELSE CHARINDEX(' ', billcode) END) AS billcode
FROM
      TableName
0
 
romeiovasuAuthor Commented:
how to do the same thing in access
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.