locked
Sorting the text data RRS feed

  • Question

  • Hi

    I have a column in which the data is in string format shown in below image ( Column A). The out put should be sorted from lower to bottom with space in between. ( i.e 102 MB)

    I am using Netezza as database and PLSQL.



    • Edited by Fareed37 Wednesday, October 21, 2020 6:17 PM
    Wednesday, October 21, 2020 6:13 PM

All replies


  • Hi Fareed37, 
    Based on your description, I have a suggestion you can refer to.
    First, you can store the data of the data table in an array.
    Second, perform two bubble sorting:
    1. The string containing MB is ranked first, 
    2. The string is converted to the corresponding number for sorting. 
    Finally, you can insert a space in the third position from the bottom of each string in the obtained array.
    Here is my test code:(in my test, I convert array to datatable in the end)

                string[] array = new String[] { "5GB", "50GB", "500MB", "1GB", "14GB", "11GB", "10GB", "102MB" };
                for (int i = 0; i < array.Length; i++)    
                {
                    for (int j = i; j > 0; j--)
                    {
                        if (Int32.Parse(array[j].Substring(0, array[j].Length - 2)) > Int32.Parse(array[j - 1].Substring(0, array[j - 1].Length - 2))) break;  //If the number to be sorted is greater than the maximum value of the sorted elements, there is no need to compare. Otherwise, you have to keep comparing to find the right place
                        else
                        {
                            string sap = array[j];
                            array[j] = array[j - 1];
                            array[j - 1] = sap;
                        }
                    }
                }
                int l = 0;
                while (l < array.Length)
                {
                    for (int i = 0; i < array.Length - 1; i++)
                    {
                        if (array[i + 1].Contains("MB"))
                        {
                            string S = array[i];
    
                            array[i] = array[i + 1];
    
                            array[i + 1] = S;
    
                        }
                    }
                    l++;
    
                }
                DataTable dt = new DataTable();
    
                dt.Columns.Add("ColumnA");
                foreach (String st in array)
                {
                 //insert a space in the third position from the bottom of each string
                   string result = st.Insert(st.Length-2," ");
                   dt.Rows.Add(result);
                 //  Console.WriteLine(result);
                }

    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 22, 2020 6:13 AM
  • Hi Daniel

    Thank you for this code.. Actually I am using netezza and PLSQL. Looks like the above code is in C language.

    Actually, I have one of the columns with this data  column A : "5GB", "50GB", "500MB", "1GB", "14GB", "11GB", "10GB", "102MB"

    either i have to write a variable or derive a new column.

    ------

    Select ColumnA

    from table test1

    -----

    Thanks

    Fareed

    Thursday, October 22, 2020 6:59 AM
  • Hi Fareed37,
    First, I can't do it in netezza and I suggest you try to implement it according to my ideas.
    Meanwhile, this forum mainly talks about linq to sql.
    For T-sql questions, I suggest you go to the "sql-server-transact-sql" tab in Microsoft QA forum.
    Thank you for your understanding.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 23, 2020 8:57 AM
  • Check if this query works on your server:

    select replace(replace(ColumnA, 'GB', ' GB'), 'MB', ' MB') as ColumnA

    from MyTable

    order by cast(replace(replace(ColumnA, 'MB', ''), 'GB', '000') as int)

     

    Friday, October 23, 2020 4:21 PM