Order a varchar field
-
Hello everyone,
Is there any way to get data like the following
JJ1
JJ102
JJ122
JJ2
JJ3
To order like this
JJ1
JJ2
JJ3
JJ102
JJ122
Thanks,
Keith
-
Try something like:
SELECT YourCol
FROM YourTable
ORDER BY
LEFT(YourCol, 2)
,CAST(SUBSTRING(YourCol, 3, 3) AS int)
-
Hey Whats up
Ok I did, but hopefully you dont mind doing it this was
1. First Create this Function on your DB, What the Function does is return Numerci values, Proudly Home made by me
---------------------------------------------------------
CREATE FUNCTION [dbo].[func_Return_Numeric] --SELECT ISNULL(dbo.func_Return_Numeric(NULL),0), LEN(dbo.func_Return_Numeric('23123fsdf111111'))
(@String AS VARCHAR(1024))
RETURNS VARCHAR(1024)
AS
BEGIN --Of Function
-----------------------------------
DECLARE @Position AS INT,
@StrLen AS INT,
@Temp_Character AS VARCHAR,
@Final_String AS VARCHAR(1024)
SET @String = LOWER(@String)
SET @StrLen = Len(@String)
SET @Position = 1
SET @Final_String = ''
--To Quit The Loop If The String is 0
IF @StrLen = 0 OR @String = ' ' OR @String IS NULL OR @String = '' OR @String = 'NULL'
BEGIN
SET @Position = @Strlen + 10
SET @Final_String = 0
END
WHILE (@Position <= @StrLen)
BEGIN
SET @Temp_Character = SUBSTRING(@String, @Position,1)
IF @Temp_Character LIKE '[0-9]'
SET @Final_String = @Final_String + @Temp_Character
SET @Position = @Position + 1
END -- Of While Loop
----
-----------------------------------------------------------------------------
IF LEN(@Final_String) = 0
SET @Final_String = NULL
RETURN CONVERT(VARCHAR(1024), @Final_String)
END -- Of Function
------------------------------------------------------
2. Then I created a Table with your Values, and this is how I Queried the results and it worked, please reply back
SELECT
Field1,
CONVERT(INT, dbo.func_Return_Numeric(Field1))
FROM
Table1
ORDER BY
CONVERT(INT, dbo.func_Return_Numeric(Field1))
-
Thanks for the quick reply. I should have given a better data sample, Sorry. When I try
SELECT YourCol
FROM YourTable
ORDER BY
LEFT(YourCol, 2)
,CAST(SUBSTRING(YourCol, 3, 3) AS int)
it works great for the original data set I gave. Unfortunately the original table has a mixture of over 800 values. The field in question has anywhere from 1-3 letters at the beginning followed by 1-3 numbers. Below is a little better sample.
JJ1
JJ102
JJ122
JJ2
JJ3
P12
P140
RTY12
RTY4
Thanks,
Keith
-
did you take a look at my post ?
-
Just tried your function. The new data set comes back like below. It doesn't seem to do well if there are two of the same number even though the beginning letters are different. Can that be fixed?
Col1
JJ1 1
JJ2 2
RTY4 4
RTY12 12
P12 12
JJ102 102
JJ122 122
P140 140
-
ok Hear goes another one, use the same method, just here is the second function
1. Create the Second Function, still proudly made by me
-----------------------------------------------------
CREATE FUNCTION [dbo].[func_Return_String] -- SELECT dbo.func_Return_String('123asdf23')
(@String AS VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
-----------------------------------
DECLARE @Position AS INT,
@StrLen AS INT,
@Temp_Character AS VARCHAR(1),
@Final_String AS VARCHAR(256)
SET @StrLen = Len(@String)
SET @Position = 1
SET @Final_String = ''
--To Quit The Loop If The String is 0
IF @StrLen IS NULL OR @String = ''
RETURN NULL
WHILE (@Position <= @StrLen)
BEGIN
SET @Temp_Character = SUBSTRING(@String, @Position,1)
IF (@Temp_Character LIKE '[A-Z]') OR @Temp_Character LIKE '[a-z]'
SET @Final_String = @Final_String + @Temp_Character
SET @Position = @Position + 1
END -- Of While Loop
----
--ADDED DOUBLE CHECK
--------------------------------------------------------------------------------
SET @Final_String = LTRIM(RTRIM(REPLACE(@Final_String,' ','')))
----------------------------------------------------------------------------------
IF LEN(@Final_String) < 1
SET @Final_String = NULL
RETURN CONVERT(VARCHAR(30), @Final_String)
END
GO
-----------------------------------------------------
2. Then Comes the second script
SELECT
Field1,
dbo.func_Return_String(Field1),
CONVERT(INT, dbo.func_Return_Numeric(Field1))
FROM
Table1
ORDER BY
dbo.func_Return_String(Field1),
CONVERT(INT, dbo.func_Return_Numeric(Field1))
Please reply before I leave to go home
-
The new function does work for the sample data set. When I get a chance I am going to try it on a larger data set and will let you know if it doesn't work. Thanks for your creative solution and quick replies.
-
Wilbur gave a good solution that involved using a function and I appreciate it. I was just curious if anyone else had a solution that could be more portable (with some obvious tweaks) between platforms such as Oracle, Access, etc. I have a feeling that I will posed with that problem shortly.
Thanks,
Keith
-
This should take into account your extra data:
SELECT YourCol
FROM YourTable
ORDER BY
LEFT(YourCol, PATINDEX('%[0-9]%', YourCol) - 1)
,CAST(SUBSTRING(YourCol, PATINDEX('%[0-9]%', YourCol), 3) AS int)
-
Thanks Ken. Your last post sorts the data great.
-
Try this:
SELECTcVal, CONVERT(INTEGER, STUFF(cVal, 1, PATINDEX('%[0-9]%', cVal) -1, ''))
FROM #tmp_holding
ORDER BY 2, 1
HTH,
P
-
Here's the correct version (single column in select, ordering not by ordinal position - is that still to be deprecated?)
SELECTcVal
FROM #tmp_holding
ORDER BY CONVERT(INTEGER, STUFF(cVal, 1, PATINDEX('%[0-9]%', cVal) -1, '')), cVal
P
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply