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

Substring or Charindex?

I'm trying to only pull the Invoice Number from this data:

InvoiceNo: 14307; Total Amount: $11.72
InvoiceNo: 10444; Total Amount: $40.91
InvoiceNo: 12281; Total Amount: $40.91
InvoiceNo: 8705; Total Amount: $40.91
InvoiceNo: 11503; Total Amount: $35.16
InvoiceNo: 10232; Total Amount: $75.83
InvoiceNo: 12609; Total Amount: $40.91
InvoiceNo: 13401; Total Amount: $15.00
InvoiceNo: 5991; Total Amount: $22.34
0
Nathan Riley
Asked:
Nathan Riley
1 Solution
 
bull_riderCommented:
I would say SUBSTRING as well as CHARINDEX to get the invoice value. CHARINDEX to gethe index of the semicolon and substrng to retrieve all characters from 12th position to the charindex of the semicolon.
0
 
momi_sabagCommented:
select substring(col_name, 12, charindex(';') - 12 )
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
select substring( left(yourfield, charindex(';', yourfield)-1), charindex(' ', yourfield)+1, 100)

Open in new window

0
 
bokistCommented:
Try this way

select ltrim(rtrim(substring(invoiceNo, charindex(':', invoiceNo) +1, charindex(';', InvoiceNo) -1)))
0
 
bokistCommented:
Little bit too late, now I learned to refresh allways before posting
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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