Solved

How to sum / values in part and underline through SQL

Posted on 2014-03-21
5
237 Views
Last Modified: 2014-03-21
Dear Experts,
I have two fileds, which in need to sum

1. 100/98
2. 10/9

need to show result with under line

110/107

Please help
0
Comment
Question by:Mehram
  • 3
5 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39944957
there is nothing built-in to do it. you will need to find the / to extract the 2 numbers of each fraction, add them and them concat back a string
0
 

Author Comment

by:Mehram
ID: 39944960
can you help to build this
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39944998
your fields are stored as varchar(x)?
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 39945013
try this:

DECLARE @v1 VARCHAR(10) = '100/98'
DECLARE @v2 VARCHAR(10) = '10/9'

DECLARE @v1_1 INT
DECLARE @v1_2 INT
DECLARE @v2_1 INT
DECLARE @v2_2 INT

DECLARE @posv1 int = CHARINDEX('/', @v1)
DECLARE @posv2 int = CHARINDEX('/', @v2)

DECLARE @result VARCHAR(10)

IF (@posv1 = 0) OR (@posv2 = 0)
BEGIN
      SELECT 'invalid values'
END
ELSE
BEGIN
      SELECT @v1, @posv1, @v2, @posv2

      SET @v1_1 = CAST(SUBSTRING(@v1, 1, @posv1-1) AS INT)
      SET @v1_2 = CAST(SUBSTRING(@v1, @posv1+1, 99) AS INT)

      SET @v2_1 = CAST(SUBSTRING(@v2, 1, @posv2-1) AS INT)
      SET @v2_2 = CAST(SUBSTRING(@v2, @posv2+1, 99) AS INT)

      SET @result = CAST(@v1_1+@v2_1 AS VARCHAR) + '/' + CAST(@v1_2+@v2_2 AS VARCHAR)

      SELECT @v1_1, @v1_2, @v2_1, @v2_2, @result
END
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39945031
>need to show result with under line
For starters, SQL Server is a database, and does not graphically render such as underscore.

If you need to perform math on two columns, I highly recommend running some code similar to Eric's above that searches for the /, writes the two numbers into two new columns, then you can SUM() normally.

Otherwise, Eric's T-SQL above is the correct answer (nice job btw).
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
using t-sql EXISTS 8 39
Filtered index 5 55
SQL Query 3 48
SQL Server CASE .. WHEN .. IN statement - Syntax issue 4 49
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now