Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
 Posted - 2015-04-20 : 12:20:53
|
Hi Firends,
I have a field called CustomerRange which has values in the following way for some Customers:
Customer ID, CustomerRange
1111,[0-1]
1111,[1-2]
1111,[2-3]
And for some Customers
2222, 1
2222, 3
2222, 6
So bascially i want to write a select statement ordering the Customer range ..Its a text field in the database..How can i achieve this |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2015-04-20 : 12:29:05
|
if the single values are to be interpreted as being a range that is the start and end , i.e. 1 could be interpreted as [1-1] then you could use something like the following
;with MyCTE
AS
(
SELECT 1111 CustID,'[0-1]' TRange UNION
SELECT 1111,'[1-2]' UNION
SELECT 1111,'[2-3]' UNION
SELECT 2222, '1' UNION
SELECT 2222, '3' UNION
SELECT 2222, '6'
)
SELECT *, CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END SortedBy FROM MyCTE
ORDER By CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END
|
 |
|
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2015-04-20 : 13:10:22
|
| Thanks Michael but i ma looking if there is function or something i can use cause the values keep changing and somtimes could be 20 records. |
|
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2015-04-20 : 13:23:40
|
This was an example.
You would use this, but against your table
SELECT COLUMN FROM TABLE
ORDER By CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END
but you could turn that could into a function.
Or have I misunderstood. Could you give me an example of what might change? |
|
|
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2015-04-20 : 15:48:04
|
Thank You Michae.It seems to work for the range values but the order by is not working when they are not ranges but simple integers as provided in my above example
2222, 1
2222, 3
2222, 6
They are not sorting in order.Thanks for keeping up with me..
|
|
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2015-04-21 : 13:06:27
|
It should work for both -
;with MyCTE
AS
(
--SELECT 1111 CustID,'[0-1]' TRange UNION
--SELECT 1111,'[1-2]' UNION
--SELECT 1111,'[2-3]' UNION
SELECT 2222 CustID, '1' TRange UNION
SELECT 2222, '3' UNION
SELECT 2222, '6'
)
SELECT *, CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END SortedBy FROM MyCTE
ORDER By CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END |
|
|
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2015-04-21 : 15:08:22
|
;with MyCTE
AS
(
SELECT 2222 CustID,'11' TRange UNION
SELECT 2222, '3' UNION
SELECT 2222, '6'
)
SELECT *, CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END SortedBy FROM MyCTE
ORDER By CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END
Please check this example..This is not ordering correctly when the integer is greater 9 . Thank you |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-04-22 : 02:02:32
|
Can try this but not sure if the ordering will be as per your requirement:
SELECT *,
REPLACE(REPLACE(REPLACE(TRange,'[',''),']',''),'-','') AS SortedBy
FROM MyCTE
ORDER By CAST(REPLACE(REPLACE(REPLACE(TRange,'[',''),']',''),'-','') AS INT)
--------------------
Rock n Roll with SQL |
|
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2015-04-22 : 07:21:34
|
Apologies: I coded with the data provided.
RocknPops solution will work -- revised
It actually will not work as [-1-2] is interpreted as 12
1111 [0-1] 01
2222 1 1
2222 3 3
2222 6 6
2222 11 11
1111 [1-2] 12
1111 [2-3] 23
2222 [78-99] 7899
I think I may even need a second sort - unless you don't care that [1-2] is before 1
1111 [0-1] 0
1111 [1-2] 1
2222 1 1
1111 [2-3] 2
2222 3 3
2222 6 6
2222 11 11
2222 [78-99] 78
;with MyCTE
AS
(
SELECT 1111 CustID,'[0-1]' TRange UNION
SELECT 1111,'[1-2]' UNION
SELECT 1111,'[2-3]' UNION
SELECT 2222, '1' UNION
SELECT 2222, '3' UNION
SELECT 2222, '6' UNION
SELECT 2222 CustID,'11' TRange UNION
SELECT 2222, '[78-99]' UNION
SELECT 2222, '6'
)
SELECT * ,CASE WHEN CHARINDEX('[',TRange) = 0 THEN TRange ELSE SUBSTRING(TRange,2,CHARINDEX('-',TRange,1)-2 ) END
FROM MyCTE
ORDER By CAST(CASE WHEN CHARINDEX('[',TRange) = 0 THEN TRange ELSE SUBSTRING(TRange,2,CHARINDEX('-',TRange,1)-2 ) END AS INT)
|
|
|
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2015-04-22 : 17:00:10
|
Thank you all for your help.
With the help google and online friend This is what i got.
But For another table the value of ranges do not have [] braces. they are just 0-1,1-3,7-8,3-6. In this scenario. How can i change the below code to work. Any help would be appreciated.
;WITH MyCTE
AS
(
SELECT 1111 CustID,CustomerRange
FROM (VALUES('[0-1]'),('[10-13]'),('[7-10]')) AS A(CustomerRange)
UNION ALL
SELECT 2222,CustomerRange
FROM (VALUES('11'),('3'),('14')) B(CustomerRange)
)
SELECT CustID,
CASE
WHEN CHARINDEX('[',CustomerRange) = 0
THEN CONCAT('[',CustomerRange,'-',CustomerRange,']')
ELSE CustomerRange
END AS CustomerRange, --Formatting it
order_col
FROM MyCTE
CROSS APPLY (SELECT CAST(CASE WHEN CHARINDEX('-',CustomerRange) > 0
THEN SUBSTRING(CustomerRange,2,CHARINDEX('-',CustomerRange) - 2)
ELSE CustomerRange
END AS INT)
) CA(order_col)
ORDER BY custID,order_col |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-04-23 : 06:46:10
|
Michael's solution would work, your last solution is the same. Just need to handle for 0-1,1-3...
here you go:
SELECT * ,
CAST(CASE WHEN CHARINDEX('-',TRange) > 0 AND CHARINDEX('[',TRange) > 0
THEN SUBSTRING(TRange,2,CHARINDEX('-',TRange)-2)
WHEN CHARINDEX('[',TRange) = 0 AND CHARINDEX('-',TRange) >0
THEN SUBSTRING(TRange,1,CHARINDEX('-',TRange)-1)
ELSE TRange
END
AS INT)
FROM MyCTE
ORDER By CustID,
CAST(CASE WHEN CHARINDEX('-',TRange) > 0 AND CHARINDEX('[',TRange) > 0
THEN SUBSTRING(TRange,2,CHARINDEX('-',TRange)-2)
WHEN CHARINDEX('[',TRange) = 0 AND CHARINDEX('-',TRange) >0
THEN SUBSTRING(TRange,1,CHARINDEX('-',TRange)-1)
ELSE TRange
END
AS INT)
--------------------
Rock n Roll with SQL |
|
|
|
|
|
|
|
|
Need to order by Range Values?