I have a column batch code having any combination of alphabet and numberI want max of this column

Question

I have a column batch code having any combination of alphabet and number(either any one or both are allowed) eg, "101,102,102.aaa, a103,b101,a104". i want to get max of this column, which would be "a104".(max of int part only), can any body help i have alredy ussed

SELECT barcode
from tbl_Batch 
ORDER BY CAST(barcode as UNSIGNED) DESC;

and getting error

"Msg 243, Level 16, State 1, Line 5

Type UNSIGNED is not a defined system type."

I am using sql server 2008 r2

Please look at https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string All you need to do is get max afterwards — Jonathan Willcock, Jul 25 '17 at 06:59
Can a barcode contain data like `101.ggr.304`? If so, how should we treat the numeric value? Is it 101, 304, or 101304? — Zohar Peled, Jul 25 '17 at 07:11
no it may contain "ggr304" symbols are not allowed, only alphaber or number or both — Karuna Soni, Jul 25 '17 at 07:18

score 0 · Accepted Answer · answered Jul 25 '17 at 07:18

SQL Server does not have a data type called UNSIGNED, that's why you've got that error.
If anything, you should cast to int. However, casting a string like a104 to int will raise an error.
So You'll need to first extract the numeric values and only then you can get the max value from your table.

I'm assuming that all non numeric values are in grouped together.
This technique will not work for values like asdf123be.
It will treat values like 123te43 as 12343.

Basically what I've done is replaced every digit with an empty string and then replaced the outcome of that with an empty string. There are other ways to extract numbers from a string in sql, so if you have values that are not suited for this technique you can choose a different one.

Create and populate sample table (Please save us this step in your future questions)

declare @T as table
(
    barcode varchar(10)
)

insert into @T values
('101'), ('102'), ('102.aaa'), ('a103'), ('b101'), ('a104')

The query:

select  top 1 barcode
from @T
order by cast( 
            replace(barcode,    
                replace(
                    replace(
                        replace(
                            replace(
                                replace(
                                    replace(
                                        replace(
                                            replace(
                                                replace(
                                                    replace(barcode, '0', '')
                                                , '1', '')
                                            , '2', '')
                                        , '3', '')
                                    , '4', '')
                                , '5', '')
                            , '6', '')
                        , '7', '')
                    , '8', '')
                , '9', '')
            , '') 
         as int) desc

Result - a104.

You can see a live demo on rextester.

[Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) — Zohar Peled, Jul 25 '17 at 07:25

I have a column batch code having any combination of alphabet and numberI want max of this column

1 Answers1