0

This is the table which I have by name project and it contains 3 columns:

  1. estimateId

  2. name

  3. projectName

I want to fetch data from SQL database based on maximum value of estimateId but here estimateid is alphanumeric. How can I achieve this.

I need a SQL query to achieve this:

For example estimateId contains values like:

Elooo1
Elooo2
......      
Elooo10

and so on. So how can I achieve this?

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • Please tag your question with the database that you are using: mysql, oracle, postgresql...? The version of the database product may also be a useful information. – GMB Jun 11 '20 at 09:15
  • I just started `SQL` like today.. so i'm totally a newbie, but I think I could solve your problem. I would do something like this `SELECT name, projectName FROM table ORDER BY estimateId ASC` or `SELECT name, projectName FROM table ORDER BY estimateId DESC` read up on `ORDER BY` command https://www.w3schools.com/sql/sql_orderby.asp – SSpoke Jun 11 '20 at 09:22
  • i am using Microsoft sql server management studio 2017 version –  Jun 11 '20 at 09:26

3 Answers3

1

Setup Testing Data

DECLARE @tmpTable TABLE ( estimateId NVARCHAR(MAX));
INSERT into @tmpTable(estimateId) VALUES ('Elooo1'),('Elooo2'),('Elooo3'),('Elooo4'),('Elooo5'),('Elooo6');

Split data based on the pattern

SELECT T.prefix AS prefix, MAX(T.suffix) AS suffix, MAX(estimateId) AS estimateId FROM (SELECT estimateId,LEFT(estimateId, PATINDEX('%[a-zA-Z][^a-zA-Z]%', estimateId )) AS prefix,LTRIM(RIGHT(estimateId, LEN(estimateId) - PATINDEX('%[a-zA-Z][^a-zA-Z]%', estimateId ))) As suffix FROM @tmpTable) T GROUP BY T.prefix

Result

prefix  suffix  estimateId
Elooo   6   Elooo6

Reference

split alpha and numeric using sql

Dale K
  • 25,246
  • 15
  • 42
  • 71
Wayne
  • 11
  • 2
0

I just started SQL like today.. so i'm totally a newbie, but I think I could solve your problem. I would do something like this

SELECT name, projectName FROM table ORDER BY estimateId ASC

or (I think you will need ORDER BY ... DESC)

SELECT name, projectName FROM table ORDER BY estimateId DESC

SSpoke
  • 5,656
  • 10
  • 72
  • 124
0

You seem to be looking to extract the numeric part of the strings. Assuming that the strings have variable length, and that the numbers are always at the end, you can do:

try_cast(
    substring(estimateId, patindex('%[0-9]%', estimateId), len(estimateId)) 
    as int
)

This captures everything from the the first number in the string to the end of the string, and attempts to convert it to a number (if the conversion fails, try_cast() returns null rather than raising an error).

It is not very clear what you want to use this information for. For example, if you wanted to sort your data accordingly, you would do:

select *
from mytable
order by try_cast(
    substring(estimateId, patindex('%[0-9]%', estimateId), len(estimateId)) 
    as int
)
GMB
  • 216,147
  • 25
  • 84
  • 135