August 17, 2022 at 12:04 pm
Hi everyone,
I have a column in my SQL table named: File_Names.
In this column I have data like: Individual_1.xml, Individual_2.xml, Individual_3.xml, Individual_4.xml,.... so on.
When I run this query:
select
distinct
File_Names
from Individual
where File_Names NOT like '%UPDATE%'
order by file_names asc
I get result like this:
Individual_1.xml
Individual_10.xml
Individual_11.xml
Individual_12.xml
Individual_13.xml
Individual_14.xml
Individual_15.xml
Individual_16.xml
Individual_17.xml
Individual_18.xml
Individual_19.xml
Individual_2.xml
Individual_20.xml
Individual_21.xml
Individual_22.xml
Individual_23.xml
Individual_3.xml
Individual_4.xml
Individual_5.xml
Individual_6.xml
Individual_7.xml
I need result like this:
Individual_1.xml
Individual_2.xml
Individual_3.xml
Individual_4.xml
Individual_5.xml
Individual_6.xml
Individual_7.xml
.
.
.
Individual_23.xml
August 17, 2022 at 12:44 pm
That's one of the joys of sorting strings.
You would need to strip out all the alpha data and convert the remaining numeric data to an int, then sort on that column instead.
Using your sample data, below is one way to do it.
create table #filenames (fn varchar(50))
insert into #filenames values
('Individual_1.xml' )
,('Individual_10.xml')
,('Individual_11.xml')
,('Individual_12.xml')
,('Individual_13.xml')
,('Individual_14.xml')
,('Individual_15.xml')
,('Individual_16.xml')
,('Individual_17.xml')
,('Individual_18.xml')
,('Individual_19.xml')
,('Individual_2.xml' )
,('Individual_20.xml')
,('Individual_21.xml')
,('Individual_22.xml')
,('Individual_23.xml')
,('Individual_3.xml' )
,('Individual_4.xml' )
,('Individual_5.xml' )
,('Individual_6.xml' )
,('Individual_7.xml' )
SELECT fn, convert(int,SUBSTRING(fn, charindex('_',fn,1)+1,charindex('.',fn,1)-charindex('_',fn,1)-1)) AS OrderingKey from #filenames
order by OrderingKey
August 17, 2022 at 2:02 pm
If you can get rid of the DISTINCT, you can do as below. If not, let me know and I will adjust code to work around the DISTINCT.
select
File_Names
from (
select 'Individual_1.xml' as file_names union all
select 'Individual_10.xml' union all
select 'Individual_7.xml' union all
select 'Individualx_1.xml' union all
select 'Individualx_10.xml' union all
select 'Individualx_7.xml' union all
select 'Individualy_.xml' union all
select 'Individualz_.xml'
) as Individual
where File_Names NOT like '%UPDATE%'
order by left(file_names, patindex('%[0-9]%', file_names + '0') - 1),
substring(file_names, nullif(patindex('%[0-9]%', file_names), 0) - 1, 8000)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 18, 2022 at 5:49 am
This was removed by the editor as SPAM
August 22, 2022 at 7:35 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply