April 4, 2016 at 2:40 am
Can anyone help me to solve the below issue?
Issue:
I need to sort alphanumeric value based on the condition.
Sample Table:
TableA
ID
1
2
2A
200
1000
11
90
20
2011
My table name is TableA and field ID have above values. When user fires the condition like "Get values less than 20" then I need to pull the data from TableA from the field ID as below
ID
-----
1
2
2A
11
20
I tried using PATINDEX still no luck.
LEFT(ID,PATINDEX('%[0-9]%',ID)-1)
Please help me
April 4, 2016 at 3:36 am
You're on the right track. This should work:
CREATE TABLE #John (ID varchar(4)); -- if your column is char instead of varchar, this won't work
-- Constraint to check numeric characters come before alpha
ALTER TABLE #John ADD CONSTRAINT CK_John_ID CHECK (ID NOT LIKE '%[A-Z][0-9]%');
INSERT INTO #John VALUES ('1'),('2'),('2A'),('200'),('1000'),('11'),('90'),('20'),('20AB');
SELECT ID
FROM #John
ORDER BY
CASE
WHEN ID NOT LIKE '%[A-Z]' THEN RIGHT('0000'+ID,5)
ELSE RIGHT('0000'+ID,6+LEN(ID)-PATINDEX('%[A-Z]%',ID))
END
John
April 4, 2016 at 4:01 am
Hi John,
Thanks a lot. How I can filter this with the condition? < 20.
April 4, 2016 at 4:14 am
SELECT ID
FROM #John
WHERE
CASE
WHEN ID NOT LIKE '%[A-Z]' THEN RIGHT('0000'+ID,5)
ELSE RIGHT('0000'+ID,6+LEN(ID)-PATINDEX('%[A-Z]%',ID))
END < '00020'
If you're going to use it like this, you need to make sure you understand exactly how it works, since you'll be responsible for maintaining it and answering awkward questions about it.
John
April 4, 2016 at 7:49 pm
Following data normalization rules actually helps.
If numeric and alphabetic parts of the values are meant to be processed separately then they must be stored in separate columns.
If you cannot make this world better by changing the table design than you can normalize the data on fly:
SELECT ID, CONVERT(INT,
NULLIF(
SUBSTRING(ID, 1, ISNULL(Split, LEN(ID)+1)-1)
, '')
) AS NumID,
ISNULL(
SUBSTRING (ID, Split, LEN(ID)-Split+1)
, '') CharID
FROM (
SELECT ID, NULLIF(PATINDEX('%[A-Z]%',ID), 0) Split
FROM #John j
) DT
This gives you the numeric part of the ID as integer, and the alphabetic part as string.
Further manipulations must be easy.
_____________
Code for TallyGenerator
April 4, 2016 at 9:42 pm
Just to throw another method in the ring to support Sergiy's idea of normalizing the data on the fly...
These use the trick of if you specify a length for the 3rd parameter of a string that's greater the length of the string, it'll return up to the end of the string without the SUBSTRING building in any extra characters. Also threw in an extra ISNULL(xxx,8001) to help simplify the code a bit.
--Sort numeric alpha strings
--===== If the test table exists, drop it to make reruns easier.
-- (This is not a part of the solution)
IF OBJECT_ID('tempdb..#TableA','U') IS NOT NULL
DROP TABLE #TableA
;
--===== Create and populate the test table on the fly.
-- (This is not a part of the solution)
SELECT d.ID
INTO #TableA
FROM (
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '2A' UNION ALL
SELECT '200' UNION ALL
SELECT '1000' UNION ALL
SELECT '11' UNION ALL
SELECT '90' UNION ALL
SELECT '20' UNION ALL
SELECT '2011' UNION ALL
SELECT '20AB' UNION ALL
SELECT '2b' UNION ALL
SELECT '2B' UNION ALL
SELECT '2AB' UNION ALL
SELECT '2a'
) d (ID)
;
-------------------------------------------------------------------------------
--===== Separate and sort the ID column
-- This method only works with 2005 and up.
WITH cteSplit AS
( --=== Determine where to split
SELECT ID
,SplitPos = ISNULL(NULLIF(PATINDEX('%[A-Za-z]%',ID),0),8001)
FROM #TableA src
) --=== Do the split
SELECT ID
,NumPart = CAST(SUBSTRING(ID,1,SplitPos-1) AS INT)
,ChrPart = SUBSTRING(ID,SplitPos,8000)
FROM cteSplit
ORDER BY NumPart, ChrPart
;
-------------------------------------------------------------------------------
--===== Separate and sort the ID column
-- Taking a hint from Sergiy, this method works with all versions.
SELECT ID
,NumPart = CAST(SUBSTRING(ID,1,SplitPos-1) AS INT)
,ChrPart = SUBSTRING(ID,SplitPos,8000)
FROM ( --=== Derived table to determine where to split
SELECT ID
,SplitPos = ISNULL(NULLIF(PATINDEX('%[A-Za-z]%',ID),0),8001)
FROM #TableA src
) d
ORDER BY NumPart, ChrPart
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2016 at 10:50 pm
Jeff Moden (4/4/2016)
,NumPart = CAST(SUBSTRING(ID,1,SplitPos-1) AS INT)
It will return the same zero value for "0A" and "A".
I do not like when 2 different source values turned into the same output value.
It may cause such ID's switching places in output recordset from run to run.
To avoid that I put NULLIF(...., '') in here.
it will turn "0A" into 0--"A", and "A" into NULL--"A" defining a certain ordering sequence.
_____________
Code for TallyGenerator
April 5, 2016 at 9:47 am
Sergiy (4/4/2016)
Jeff Moden (4/4/2016)
,NumPart = CAST(SUBSTRING(ID,1,SplitPos-1) AS INT)
It will return the same zero value for "0A" and "A".
I do not like when 2 different source values turned into the same output value.
It may cause such ID's switching places in output recordset from run to run.
To avoid that I put NULLIF(...., '') in here.
it will turn "0A" into 0--"A", and "A" into NULL--"A" defining a certain ordering sequence.
Ah... I see what you mean. I made the bad assumption that the numeric part would always be present. Thanks, Sergiy.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply