• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

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.
0
gogetsome
Asked:
gogetsome
  • 4
  • 3
1 Solution
 
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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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:
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:
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

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now