Hi

my problem is i don't get correct result when i get max string value from my table. for example, it returns '9' as max value while i have some bigger string values like 73, 80,65, ...

i found some solutions to handle this problem (like converting to char or adding some zero in the begining of my value) but it does not appropriate for my situation because my string value maybe everything (some of my customers can use numbers while some other else can use alphabetical+numbers)!.

can anybody help me ?

thanks in advance

Can you supply more detailed information about the schema, data types, query you are using, etc?  Can you post DDL for object creation?

Thanks,
Sam Lester (MSFT)

Your talking about this case:

SELECT	MAX(s), MIN(s)
FROM	( VALUES ('9') , ( '73' ), ( '80' ), ( '65' ), ( 'A9' ) ) T (s);

Then you're basically tring to compare appels and oranges, when your input is undefined. A MAX() requires a greater relation on the domain values. And per default this can only be the alphanumerical sort order. The only solution is a function which mappes these codes based its class - in most cases the customer - into onther domain where you have such a greater relation which produces your desired output.

• Proposed as answer by Dan GuzmanMVP Sunday, August 12, 2012 4:59 PM
• Unproposed as answer by Hamed_1983 Sunday, August 12, 2012 8:50 PM

Permit me to point out that '9' is a bigger string value than '83', just like 'H' is a bigger string value than 'Glockenspiel'.

Strings are compared so that first the computer compares the first characters in the two strings, and if they are equal, the computers compares the second and so on. Things get a little more complicated when the characters are only different in case or by an accent, or if there are multi-character sequences that have special sort rules, but we can disregard this here.

It seems that you want the strings to sort as numbers, which is not exactly trivial if you have strings like '9', '83', 'B123', '7And9' etc. Not that it can't be done; Windows Explorer performs some kind of "smart" sorting, but there is no built-in support for this in SQL Server, but you would have to roll your own, which certainly would be expensive if you have any volumes of data.

And this case you are not talking about the output of the lot, but only the max value. I can't really see the business logic that requires that strings are treated as numbers here, at least not if the strings can have alphabetic contents. (And if alphacontents was not permitted, they should not be strings in the first place.)

I have a table structure is something like this :

Patients(PatientCaseID nvarchar(50) primary key, PatientFName nvarchar(50), PatientLName nvarchar(50))

As u see the PatientCaseID is pk and nvarchar(50). some doctors use 1,2,3,4,5,etc as PatientCaseID while some other doctors use composite values like p1001,p1002,p2003,p2004,etc.

I have a solution :

1. Suppose end-user has limited access to use alphabetical+numbers as PatientCaseID. for example he/can use alphabetical prefixes only in the begining of PatientCaseID value (not in every where).

2. Also suppose the prefix string is stored in application settings table. so it can be decouple from the whole string.

3. Now, i must decouple the prefix string, the convert remaining string to int and get max value, then add prefix to new value.

Any other idea ?

HI Hameed !

You may get the desired output using below query;

CREATE TABLE #Temp (PatientID VARCHAR(100))
--DROP TABLE #Temp
INSERT INTO #Temp
SELECT    '100' UNION ALL
SELECT    'PTR-1250' UNION ALL
SELECT    'PTR-200' UNION ALL
SELECT    '110' UNION ALL
SELECT    '900'

SELECT    CAST(SUBSTRING(PatientID, PATINDEX('%' + '[0-9]' + '%',PatientID), LEN(PatientID)) AS INT) AS PatientID
FROM    #Temp

Note : You can adjust it according to your requirements.

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

• Marked as answer by Hamed_1983 Sunday, August 12, 2012 10:08 PM

Your plan appears sound to me. I'm assuming that you are selecting the MAX value to get the next key. But to do that, you must also have control over the data like you suggest. It does not really makes sense to have users enter the surrogate key themselves.

Then again, why use prefixes at all? An nvarchar key is less efficient than an int key. Also, the collation will play tricks with you. Is p001 the same value as P001? And what about P0001 is that the same as P000000001?

When I see NVARCHAR(50)(50) I am pretty sure the data was declared by an ACCESS programmer who has no idea what he is doing. Do you really have first and last names that need fifty UNICODE characters ?  You will get them!  If you invite garbage data, it will come. The USPS uses VARCHAR(20) for names; this has to do with the size of mailing labels and the fact that Latin-1 is required by ISO in ALL languages. 

lease read the  "Stairway to Data" series and pay attention to last sections on the design of encoding schemes. What you have is useless and dangerous because it has no data integrity.  Tag numbers (learn what that means, please) need a regular expression and I like to have a check digit. My first guess, without an specs is that you need something like this:  

patient _case_nbr CHAR() NOT NULL PRIMARY KEY
 CHECK (patient _case_nbr 
  LIKE '[ ABC][0-9][0-9][0-9][0-9][0-9]')

Fixed length for the forms and display, characters limtied to the Latin-1 Unicode set, and easy to sort. 

 http://www.sqlservercentral.com/articles/Database+Design/72612/)

Thanks Hasham

this is what i'm looking for! it works fine for every strings.

thanks again

yeah...it work, if any one want to have max id then just create a view

eg: create view demoview

as

SELECT    CAST(SUBSTRING(PatientID, PATINDEX('%' + '[0-9]' + '%',PatientID), LEN(PatientID)) AS INT) AS PatientID
FROM    #Temp

now a virutal table is created,now if u want max id then just right this query "SELECT MAX(PatientID) FROM demoview

thanks

harjeet singh

if you want  to have max id then just create a view

eg: create view demoview

as

SELECT    CAST(SUBSTRING(PatientID, PATINDEX('%' + '[0-9]' + '%',PatientID), LEN(PatientID)) AS INT) AS PatientID
FROM    #Temp

now a virutal table is created,now if u want max id then just right this query "SELECT MAX(PatientID) FROM demoview

thanks

harjeet singh