?
Solved

How to sum / values in part and underline through SQL

Posted on 2014-03-21
5
Medium Priority
?
251 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 70

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 70

Expert Comment

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

Accepted Solution

by:
Éric Moreau earned 2000 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 66

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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