Web design and hosting, database, cloud and social media solutions that deliver real business results
  • Business Solutions
    • Robotic Process Automation
    • Bespoke Software
    • Database Services
      • Data Integration
      • Datawarehouse Services
      • Power BI
    • Web Services
      • Logo Design
      • Payment Gateways
      • Web Site Optimisation
      • Web Site Security
      • Technical Tools
    • Cloud Services
      • Amazon Web Services
      • Google Cloud Services
      • Microsoft Azure
    • Microsoft Office
    • Social Media Services
  • Academy
    • Our Test Environment
    • Learning Databases
      • The Basics
      • Get Open Query
      • SQL Server Data
      • SQL Server Maintenance
      • Using SQL Server Dates
      • Using SQL Server Functions
      • Using SQL Server Pivot-Unpivot
      • Technical Tools
    • Learning Web Design
      • Building Ousia Content Management System
      • Using ASP-NET
      • Using CSS
      • Using JavaScript
    • Learning Cloud and IT Services
      • Task Scheduler Error 2147943645
      • Requesting SSL and Generation of PFX file in OpenSSL Simple Steps
    • Using Social Media
      • Asking for a Google Review
      • Changing a Facebook account from personal to business
      • Choosing where to focus Social Media effort
      • Social Media Image Sizes
      • Using Meta Data to set Social Media Images
  • About
    • Blog
      • Building an entry level gaming machine
      • Google Core Update Jan 2020
      • Hot Chilli Internet Closure
      • How To Choose Content For Your Website Adverts Leaflets
      • Preventing Online Scam
      • Skimmers of the gig economy
      • The most annoying things about websites on the Internet
      • Top 5 websites for free Vector Graphics
    • Portfolio
    • Team
      • Adrian Anandan
      • Ali Al Amine
      • Ayse Hur
      • Chester Copperpot
      • Fernando Ancona Camara
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
      • Trevor Momanyi
AR - عربىCS - češtinaDE - DeutschES - EspañolFA - فارسیFR - FrançaisHI - हिंदीIT - italianoJA - 日本語PL - polskiPT - PortuguêsRU - русскийTR - TürkZH - 中国的

Min and max value for columns

Creating a function to return the min and max value for columns by concatenating the strings, splitting them into rows and then returning the correct value

Info

Using SQL Server Functions

These functions will return the minimum and maximum values within a concatenated text string.

To use this you will need to create the values that you need to parse in, and also have the TextToRows function as we will convert the string into rows and parse the relevant value back.

Originally only for Integers, we added a minimum and maximum for strings too.

With the string functions you may need to change the collation and change it to a top with an order by if you are looking for case sensitive.

more: SQL Server function splitting text into data rows

SQL Code - MAX

CREATE FUNCTION [dbo].[GetStrMAX_INT](@Delim NVARCHAR(1),@Str NVARCHAR(200)) RETURNS INT AS BEGINDECLARE @RS INTSET @RS=(SELECT MAX(CONVERT(INT,REPLACE(WordStr,@Delim,'')))FROM Utilities.dbo.TextToRows(@Delim,@Str)WHERE ISNUMERIC(WordStr)=1)RETURN @RSENDGOSELECT dbo.GetStrMAX_INT(',','1,2,3,4,5,a')

SQL Code - MIN

CREATE FUNCTION [dbo].[GetStrMIN_INT](@Delim NVARCHAR(1),@Str NVARCHAR(200)) RETURNS INT AS BEGINDECLARE @RS INTSET @RS=(SELECT MIN(CONVERT(INT,REPLACE(WordStr,@Delim,'')))FROM Utilities.dbo.TextToRows(@Delim,@Str)WHERE ISNUMERIC(WordStr)=1)RETURN @RSENDGOSELECT dbo.GetStrMIN_INT(',','1,2,3,4,5,a')

We've got strings covered too...

ALTER FUNCTION [dbo].[GetStrMAX](@Delim NVARCHAR(1),@Str NVARCHAR(200)) RETURNS NVARCHAR(100) AS BEGINDECLARE @RS NVARCHAR(100)SET @RS=(SELECT MAX(CONVERT(NVARCHAR(100),REPLACE(WordStr,@Delim,'')))FROM Utilities.dbo.TextToRows(@Delim,@Str))RETURN @RSENDGOALTER FUNCTION [dbo].[GetStrMIN](@Delim NVARCHAR(1),@Str NVARCHAR(200)) RETURNS NVARCHAR(100) AS BEGINDECLARE @RS NVARCHAR(100)SET @RS=(SELECT MIN(CONVERT(NVARCHAR(100),REPLACE(WordStr,@Delim,'')))FROM Utilities.dbo.TextToRows(@Delim,@Str))RETURN @RSENDGO

Testing

SELECT dbo.GetStrMAX_INT(',','1,2,3,x,5,a') --5SELECT dbo.GetStrMIN_INT(',','1,2,3,x,5,a') --1SELECT dbo.GetStrMAX(',','1,2,3,x,5,a') --xSELECT dbo.GetStrMIN(',','1,2,3,x,5,a') --1

Please note, this commenting system is still in final testing.

Author

Gavin Clayton
Gavin Clayton
I formed Claytabase in 2010 as a way of carrying on my work with SQL Server and ASP.NET. This has culminated in the Ousia Content Management System being taken from concept to one of the quickest CMS's on the market.
Copyright Claytabase Ltd 2021, registered in England and Wales 08985867

Site Links

RSS Login ContactCookie PolicySitemap

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+442392064871info@claytabase.co.ukGround Floor, Building 1000, Lakeside North Harbour, Western Road, Portsmouth, Hampshire, United Kingdom, PO6 3EZ

Partnered With

Partners
The settings on this site are set to allow all cookies. These can be changed on our Cookie Policy & Settings page.
By continuing to use this site you agree to the use of cookies.
Ousia Logo
Ousia CMS Loader