Get values left of a period

Hello, I have two columns Bill_num and RFS in a table named sales.
Bill_num has data like this:

AG6292.0121                    
DC002050.0000                  
DC002050.0000                  
AG007240.0000                  
AG007240.0000                  
DI9773.0000                    
DI9773.0000                    
DD001140.0000                  
DD001140.0000                  
AG6283.0204          

RFS is currently null          

I need to do an update of RFS where I take what is left of the (.) in the bill_num column.
gogetsomeAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
in the UPDATE, just make it this:

update bb_master
set RFS = case when bill_num like '%.%' THEN left(bill_num, charindex(bill_num, '.')-1) ELSE bill_num end

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select case when bill_num like '%.%' THEN left(bill_num, charindex(bill_num, '.')-1) ELSE bill_num end as left_part
from yourtable
0
 
gogetsomeAuthor Commented:
Thanks AngelIII, I'm getting this error:

Invalid length parameter passed to the substring function.
0
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.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry my fault
<note to self>
  CHECK the doc before posting the charindex
</note to self>


select case when bill_num like '%.%' THEN left(bill_num, charindex('.', bill_num)-1) ELSE bill_num end as left_part
from yourtable
0
 
gogetsomeAuthor Commented:
update bb_master
set RFS = select case when bill_num like '%.%' THEN left(bill_num, charindex(bill_num, '.')-1) ELSE bill_num end as left_part
from bb_master

This is what I'm using but now get this error:
Incorrect syntax near the keyword 'select'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I suggested the SELECT syntax first so you check if the data returned is correct, before doing the update
0
 
gogetsomeAuthor Commented:
Thanks AngelIII! You ROCK!
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.