Post reply

Challenging string handling in T-SQL

  • October 23, 2017 at 12:03 am

    #339917

    Hello Experts,

    I have a data in a table as shown in the first column (A) and would like to update as shown in column B.

    Please note there is a space on both sides of the operator (+, -, /, *).

    Only these four arithmetic operators would be used. Except, operators and numerals, other words should be enclosed by [ ].

    Please do help me to achieve this either using query or using user defined function or stored procedure.

    AB
    Total Deduction  *  10[Total Deduction]  *  10
    Remium  +  0.01[Remium]  +  0.01
    100 / Final Results100 / [Final Results]
    Pre Results + 10 - New Results[Pre Results] + 10 - [New Results]

    Thanks,

    Naveen J V

  • October 23, 2017 at 4:38 am

    #1964744

    Naveen J V - Monday, October 23, 2017 12:03 AM

    Hello Experts,

    I have a data in a table as shown in the first column (A) and would like to update as shown in column B.

    Please note there is a space on both sides of the operator (+, -, /, *).

    Only these four arithmetic operators would be used. Except, operators and numerals, other words should be enclosed by [ ].

    Please do help me to achieve this either using query or using user defined function or stored procedure.

    AB
    Total Deduction  *  10[Total Deduction]  *  10
    Remium  +  0.01[Remium]  +  0.01
    100 / Final Results100 / [Final Results]
    Pre Results + 10 - New Results[Pre Results] + 10 - [New Results]

    Thanks,

    Naveen J V

    anybody attempted this questions?

  • October 23, 2017 at 7:18 am

    #1964766

    You seem to be relatively new to the forums, but you should take into account that it's a good courtesy to post your sample data in a consumable format. I'll post so you can do it like that the next time.
    Second, you'll need to get some additional information and a function from this article: Splitting Strings Based on Patterns - SQLServerCentral
    You might also need to learn a bit about patterns in SQL Server, I wrote an article some time ago: http://www.sqlservercentral.com/articles/T-SQL/130558/

    And finally, here's the code:

    CREATE TABLE #SampleData(
      AFormula varchar(1000));
    INSERT INTO #SampleData
    VALUES
      ('Total Deduction * 10'),
      ('Remium + 0.01'),
      ('100 / Final Results'),
      ('Pre Results + 10 - New Results');

    SELECT sd.AFormula,
      STUFF(( SELECT ' ' + CASE WHEN Item NOT LIKE '%[^0-9. +/*-]%' THEN LTRIM(RTRIM(Item)) ELSE QUOTENAME(LTRIM(RTRIM(Item))) END
        FROM dbo.PatternSplitCM( sd.AFormula, '%[-*/+]%') ps
        ORDER BY ps.ItemNumber
        FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
    FROM #SampleData sd;

    GO
    DROP TABLE #SampleData;

    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

  • October 23, 2017 at 9:11 am

    #1964807

    T-SQL is horrible at string manipulation.  Your best option is to use SQLCLR.

    You also need to better define your options, Specifically what do you want to happen when you have alphanumeric values like 'FY2017Q1' or 'FY2017Q2'?  I suspect you want '[FY2017Q1]', but your description could be interpreted to mean '[FY]2017

    1'.

    Finally, I hope that you're not planning to use these fields for dynamic SQL.  If you are set on doing that, please read up on SQL injection.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • October 23, 2017 at 3:16 pm

    #1964865

    Naveen J V - Monday, October 23, 2017 12:03 AM

    Hello Experts,

    I have a data in a table as shown in the first column (A) and would like to update as shown in column B.

    Please note there is a space on both sides of the operator (+, -, /, *).

    Only these four arithmetic operators would be used. Except, operators and numerals, other words should be enclosed by [ ].

    Please do help me to achieve this either using query or using user defined function or stored procedure.

    AB
    Total Deduction  *  10[Total Deduction]  *  10
    Remium  +  0.01[Remium]  +  0.01
    100 / Final Results100 / [Final Results]
    Pre Results + 10 - New Results[Pre Results] + 10 - [New Results]

    Thanks,

    Naveen J V

    How many rows are we talking about in this table???  Is it just the 4 rows you have posted?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 23, 2017 at 3:17 pm

    #1964867

    drew.allen - Monday, October 23, 2017 9:11 AM

    T-SQL is horrible at string manipulation.  Your best option is to use SQLCLR.

    You also need to better define your options, Specifically what do you want to happen when you have alphanumeric values like 'FY2017Q1' or 'FY2017Q2'?  I suspect you want '[FY2017Q1]', but your description could be interpreted to mean '[FY]2017

    1'.

    Finally, I hope that you're not planning to use these fields for dynamic SQL.  If you are set on doing that, please read up on SQL injection.

    Drew

    How would an SQLCLR be any better at this?  Are you suggesting the use of RegEx?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 23, 2017 at 10:19 pm

    #1964900

    Luis Cazares - Monday, October 23, 2017 7:18 AM

    You seem to be relatively new to the forums, but you should take into account that it's a good courtesy to post your sample data in a consumable format. I'll post so you can do it like that the next time.
    Second, you'll need to get some additional information and a function from this article: Splitting Strings Based on Patterns - SQLServerCentral
    You might also need to learn a bit about patterns in SQL Server, I wrote an article some time ago: http://www.sqlservercentral.com/articles/T-SQL/130558/

    And finally, here's the code:

    CREATE TABLE #SampleData(
      AFormula varchar(1000));
    INSERT INTO #SampleData
    VALUES
      ('Total Deduction * 10'),
      ('Remium + 0.01'),
      ('100 / Final Results'),
      ('Pre Results + 10 - New Results');

    SELECT sd.AFormula,
      STUFF(( SELECT ' ' + CASE WHEN Item NOT LIKE '%[^0-9. +/*-]%' THEN LTRIM(RTRIM(Item)) ELSE QUOTENAME(LTRIM(RTRIM(Item))) END
        FROM dbo.PatternSplitCM( sd.AFormula, '%[-*/+]%') ps
        ORDER BY ps.ItemNumber
        FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
    FROM #SampleData sd;

    GO
    DROP TABLE #SampleData;

    Thank you very much Luis. This works as I expected.
    Yes Luis, I am very new to the forum, I would take care of posting sample data in a table or in a consumable format in future.

  • October 23, 2017 at 10:26 pm

    #1964901

    Naveen J V - Monday, October 23, 2017 10:19 PM

    Luis Cazares - Monday, October 23, 2017 7:18 AM

    You seem to be relatively new to the forums, but you should take into account that it's a good courtesy to post your sample data in a consumable format. I'll post so you can do it like that the next time.
    Second, you'll need to get some additional information and a function from this article: Splitting Strings Based on Patterns - SQLServerCentral
    You might also need to learn a bit about patterns in SQL Server, I wrote an article some time ago: http://www.sqlservercentral.com/articles/T-SQL/130558/

    And finally, here's the code:

    CREATE TABLE #SampleData(
      AFormula varchar(1000));
    INSERT INTO #SampleData
    VALUES
      ('Total Deduction * 10'),
      ('Remium + 0.01'),
      ('100 / Final Results'),
      ('Pre Results + 10 - New Results');

    SELECT sd.AFormula,
      STUFF(( SELECT ' ' + CASE WHEN Item NOT LIKE '%[^0-9. +/*-]%' THEN LTRIM(RTRIM(Item)) ELSE QUOTENAME(LTRIM(RTRIM(Item))) END
        FROM dbo.PatternSplitCM( sd.AFormula, '%[-*/+]%') ps
        ORDER BY ps.ItemNumber
        FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
    FROM #SampleData sd;

    GO
    DROP TABLE #SampleData;

    Thank you very much Luis. This works as I expected.
    Yes Luis, I am very new to the forum, I would take care of posting sample data in a table or in a consumable format in future.

    Still, the question I have is how many rows do you actually need to do this for?  Also, is this a one-off or something that will need to be repeated in the future?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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