string columns containing alphanumeric

Question

Post reply

string columns containing alphanumeric

s.kushwaha1111

SSC Journeyman

Points: 89
More actions
August 27, 2015 at 3:49 am

#398950

CREATE TABLE #temp
(
Section varchar(50) NULL
)
truncate table #temp
INSERT INTO #temp (Section) VALUES ('1')
INSERT INTO #temp (Section) VALUES ('2')
INSERT INTO #temp (Section) VALUES ('11')
INSERT INTO #temp (Section) VALUES ('AB')
INSERT INTO #temp (Section) VALUES ('BC')
INSERT INTO #temp (Section) VALUES ('CD')
INSERT INTO #temp (Section) VALUES ('0115AB')
i want the result as below:-
11
2
1
0115AB
AB
BC
CD
can anybody tell me how to sort the data
Thanks
Sushil

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

for clarification, please can you explain in words the business rules for this sort order.

thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

J Livingston SQL (8/27/2015)
for clarification, please can you explain in words the business rules for this sort order.
thanks

I have to agree - the logic for the sort is not clear. If you wanted the value 0115AB at the top of the list then it would be simple (ORDER BY Section), but having the 0 come last numerically makes us wonder what your custom rules are.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

David Burrows SSC Guru Points: 65142 More actions · Answer 3

SELECT Section

FROM #temp

ORDER BY

SIGN(PATINDEX('%[a-zA-Z]%',Section)) ASC,

CAST('0'+LEFT(Section,PATINDEX('%[a-zA-Z]%',Section+'A')-1) as int) DESC,

RIGHT(Section,LEN(Section)-(PATINDEX('%[a-zA-Z]%',Section+'A')-1)) ASC

Far away is close at hand in the images of elsewhere.
Anon.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Another possibility.

SELECT *

FROM #temp

ORDER BY CASE WHEN Section NOT LIKE '%[^0-9]%' THEN CAST( Section AS int) * -1

WHEN Section LIKE '%[0-9]%' THEN 2

ELSE 3 END

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

I think that the OP needs to clarify what the "rules" are......or is this just homework (first post from OP) ?

anyways the two solutions provided give the requested results based on the sample data given by OP

However if the sample data is extended to include other possibilities,then the answers given by David and Luis are different.

CREATE TABLE #temp

(

Section varchar(50) NULL

)

INSERT INTO #temp (Section) VALUES ('1')

INSERT INTO #temp (Section) VALUES ('2')

INSERT INTO #temp (Section) VALUES ('11')

INSERT INTO #temp (Section) VALUES ('AB')

INSERT INTO #temp (Section) VALUES ('BC')

INSERT INTO #temp (Section) VALUES ('CD')

INSERT INTO #temp (Section) VALUES ('0115AB')

INSERT INTO #temp (Section) VALUES ('001abc')

INSERT INTO #temp (Section) VALUES ('001xyz')

INSERT INTO #temp (Section) VALUES ('123xyz')

INSERT INTO #temp (Section) VALUES ('123abc')

INSERT INTO #temp (Section) VALUES ('987abc')

INSERT INTO #temp (Section) VALUES ('100000')

INSERT INTO #temp (Section) VALUES ('AB0001')

SELECT Section

FROM #temp

ORDER BY

SIGN(PATINDEX('%[a-zA-Z]%',Section)) ASC,

CAST('0'+LEFT(Section,PATINDEX('%[a-zA-Z]%',Section+'A')-1) as int) DESC,

RIGHT(Section,LEN(Section)-(PATINDEX('%[a-zA-Z]%',Section+'A')-1)) ASC

SELECT *

FROM #temp

ORDER BY

CASE

WHEN Section NOT LIKE '%[^0-9]%' THEN CAST( Section AS int) * -1

WHEN Section LIKE '%[0-9]%' THEN 2

ELSE 3

END;

DROP TABLE #temp;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day