May 3, 2017 at 4:36 am
Have a column which looks like:
ABC348
DEF264
GHI071
JKL009
All I'd like to know is simply what the highest number is?
May 3, 2017 at 5:03 am
Just starting out so posting here first in case this is something stupid simple. Format is always XXX000. 348 is higher than 009. Thanks.
May 3, 2017 at 5:11 am
One can use the DigitsOnlyEE function for this
😎USE [TEEST]
GO
SET NOCOUNT ON;
DECLARE @NUMSTRING TABLE (NMSTR VARCHAR(20) NOT NULL);
INSERT INTO @NUMSTRING(NMSTR)
VALUES ('ABC348')
,('DEF264')
,('GHI071')
,('JKL009');
SELECT
NS.NMSTR
,X.DigitsOnly
FROM @NUMSTRING NS
CROSS APPLY dbo.DigitsOnlyEE(NS.NMSTR) X;
Output
NMSTR DigitsOnly
-------- -----------
ABC348 348
DEF264 264
GHI071 071
JKL009 009
To find the highest numberSELECT
MAX(CONVERT(INT,X.DigitsOnly,0)) AS MAX_NUM
FROM @NUMSTRING NS
CROSS APPLY dbo.DigitsOnlyEE(NS.NMSTR) X;
May 3, 2017 at 5:13 am
Ok, well you'll want to split the number out of the string first then. If your data is always going to be in the same format, this makes it a lot easier, as you can use the RIGHT operator. So, for example the following returns the value '99'.SELECT RIGHT('A99',2);
Think about how you would therefore apply this to your data. SQL also has aggregate functions, for example COUNT, SUM, MAX, AVG etc, one of these you will need to use. Have a go and see if you can answer this yourself with this inforamtion. if you get stuck, post what you tried and someone can show and explain where you went wrong.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 3, 2017 at 4:00 pm
If the format is always xxx###, then:
SELECT MAX(RIGHT(column, 3))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 5, 2017 at 12:28 pm
CREATE TABLE dbo.strings(string char(6), number AS Cast(RIGHT(string, 3) AS smallint) PERSISTED);
GO
CREATE CLUSTERED INDEX idx_strings ON dbo.strings(number);
INSERT dbo.strings
(string)
VALUES
('ABC348')
, ('DEF264')
, ('GHI071')
, ('JKL009');SELECT s.string
, s.number
FROM dbo.strings s
ORDER BY s.number;
May 8, 2017 at 2:23 pm
If the data in question ALWAYS has the numeric characters in the last 3 positions, there is no need to get user defined functions dragged into the picture...
It can be as simple as the following
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
AlphaNum CHAR(6) NOT null
);
INSERT #TestData (AlphaNum) VALUES
('ABC348'), ('DEF264'), ('GHI071'), ('JKL009');
SELECT
MAX(RIGHT(td.AlphaNum, 3))
FROM
#TestData td;
May 8, 2017 at 4:47 pm
Well, if you have to use a function to strip the non-numeric characters then DigitsOnlyEE is the best way to go (something I was going to say until EE beat me to it).
If the format is always CCCNNN then a few methods to solve this have been posted. Let's say we have 0 or more alphabetical characters followed by some numbers. For that we'd go with:SUBSTRING(<your text column>, PATINDEX('%[0-9]%', <your text column> ), 8000)
You could use this for a computed column (as has been mentioned) or an indexed view (which has not been mentioned). Note the following code:IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (AlphaNum varchar(10) NOT NULL);
INSERT #TestData (AlphaNum)
VALUES ('123'),('ABC348'), ('DEFG264'), ('HI071'), ('JKLMOP009'), ('X555');
SELECT MAX(num)
FROM
(
SELECT SUBSTRING(td.AlphaNum, PATINDEX('%[0-9]%', td.AlphaNum), 8000)
FROM #TestData td
) strings(num);
-- Note: the 8000 can be reduced to the size of your column
-- Itzik Ben-Gan 2001
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply