Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL  - Add numbers to a literal in query

Posted on 2016-09-23
8
Medium Priority
?
37 Views
Last Modified: 2016-11-07
i am trying to add numbers to a literal in a select query.  I want to start at a specific number and add to it for each row.  The literal is set.  Now I have something like this:

"NO"1+5 as New_FacID

which i want to look like in the New_FacID field:

NO1
NO2
NO3
NO4
NO5
NO6

because I am selecting 6 records.

Thanks.
0
Comment
Question by:donnie91910
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:donnie91910
ID: 41812756
Actually I will be putting in the starting number and then the number will increment automatically for each record in my query.

starting number at 1.  Six records are returned.

Looks like this:
NO1
NO2
NO3
NO4
NO5
NO6

Second Select statement starting number at 7.  Three records are returned.
NO7
NO8
NO9

Thanks.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 41812758
You could get the "relative record number" of each ro by running a statement like below - just replace actual table/column names in it


SELECT id, (SELECT COUNT(*) FROM table_name t2 WHERE t2.id <= t.id) AS rownumber
FROM table_name t
ORDER BY id


OR you can use that and add your specific number to the COUNT(*) to get the Starting position.

and you could concatenate those files like:



SELECT Id + CAST (  (SELECT COUNT(*) FROM table_name t2 WHERE t2.id <= t.id) AS sysname) AS  New_FacID
FROM table_name t
ORDER BY id
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 41812795
Counting all previous rows for every current row would be huge overhead.

Instead add a ROW_NUMBER() and then add that value to the starting value.

DECLARE @prefix varchar(30)
DECLARE @starting_number int
SET @prefix = 'NO'
SET @starting_number = <value_you_entered_for_starting_number>

SELECT
    @prefix + CAST(@starting_number + row_num - 1 AS varchar(10)) AS New_FacID,
    <column_name1>, <column_name2>, ...
FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY <table_column>) AS row_num
    FROM table_name
) AS derived
--ORDER BY ...
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:donnie91910
ID: 41812809
how do I hardcode the literal "NO" in my query and have the number concatinate  and increment that I have hardcoded as my starting number and have it increment for the number of rows in my query result set.

Looks like:
Select statement starting number at 7.  Three records are returned.
NO7
NO8
NO9

thanks.
0
 

Author Comment

by:donnie91910
ID: 41812813
Scott Pletcher - i'll try it
0
 

Author Comment

by:donnie91910
ID: 41812856
Scott Pletcher can the code you sent me be modified for Toad Oracle?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41812886
What is your DB? Oracle or SQL Server?
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

670 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