How can I remove the spaces from these numbers?-CodePudding

I am using SQL Server 2014 and I have a table (t1) in my database which contain a column called "MealPlan".

This column contains a list of strings (extract below):

                        MealPlan
Sansrepas315€/pers.=630€pour2pers.Devis/RésaSelectionner

Sansrepas394€/pers.=787€pour2pers.Devis/RésaSelectionner

Sansrepas547€/pers.=1 093€pour2pers.Devis/RésaSelectionner

Sansrepas547€/pers.=1 093€pour2pers.Devis/RésaSelectionner

Sansrepas700€/pers.=1 400€pour2pers.Devis/RésaSelectionner

Sansrepas328€/pers.=656€pour2pers.Devis/RésaSelectionner

I need to extract the numbers between the characters = and €

I have the following codes in place which does exactly what I need:

SUBSTRING(MealPlan,LEN(LEFT(MealPlan,CHARINDEX('=', MealPlan) 1)),LEN(MealPlan) - LEN(LEFT(MealPlan,CHARINDEX('=', MealPlan))) - LEN(RIGHT(MealPlan,CHARINDEX('€', (REVERSE(MealPlan)))))) AS [Price]

After running the above my column "Price" appear as follows:

However, I want to get rid of that space in the numbers where a thousand digit is present.

My expected output:

I have tried the following but it is not working:

REPLACE(SUBSTRING(MealPlan,LEN(LEFT(MealPlan,CHARINDEX('=', MealPlan) 1)),LEN(MealPlan) - LEN(LEFT(MealPlan,CHARINDEX('=', MealPlan))) - LEN(RIGHT(MealPlan,CHARINDEX('€', (REVERSE(MealPlan)))))), ' ','') AS [Price2]

Any help would be much appreciated.

CodePudding user response：

I just ran your query in my sample database and it is working fine..

select 
REPLACE(SUBSTRING(Description,
LEN(LEFT(Description,CHARINDEX('=', Description) 1)),
LEN(Description) - LEN(LEFT(Description,CHARINDEX('=', Description))) - LEN(RIGHT(Description,CHARINDEX('€', (REVERSE(Description)))))
), ' ','') AS [Description]
from Worker

CodePudding user response：

Table #a1

| MealPlan |
| -------- |
| Sansrepas315€/pers.=630€pour2pers.Devis/RésaSelectionner   |
| Sansrepas394€/pers.=787€pour2pers.Devis/RésaSelectionner   |
| Sansrepas547€/pers.=1 093€pour2pers.Devis/RésaSelectionner |

Query

SELECT 
        REPLACE(
            SUBSTRING(MealPlan,CHARINDEX('=', MealPlan) 1, CHARINDEX('=',REVERSE(MealPlan)) - CHARINDEX('€',REVERSE(MealPlan)) -1 )  
            ,' ', ''
        )
        as value
FROM #a1

results

value
630
787
1093

CodePudding user response：

The solution about "cut and paste" provided by Jiří Baum above did the trick for me.

CodePudding user response：

It is very easy to tokenize a string of characters by using XML and XQuery.

No need to parse string and call multiple functions: SUBSTRING(), CHARINDEX(), PATINDEX(), LEN(), REVERSE(), etc.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY(1,1) PRIMARY KEY, MealPlan NVARCHAR(1000));
INSERT INTO @tbl (MealPlan) VALUES
(N'Sansrepas315€/pers.=630€pour2pers.Devis/RésaSelectionner'),
(N'Sansrepas394€/pers.=787€pour2pers.Devis/RésaSelectionner'),
(N'Sansrepas547€/pers.=1 093€pour2pers.Devis/RésaSelectionner'),
(N'Sansrepas547€/pers.=1 093€pour2pers.Devis/RésaSelectionner'),
(N'Sansrepas700€/pers.=1 400€pour2pers.Devis/RésaSelectionner'),
(N'Sansrepas328€/pers.=656€pour2pers.Devis/RésaSelectionner');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '='
    , @euro CHAR(1) = '€';


SELECT t.* 
    , REPLACE(c.value('(/root/r[3]/text())[1]', 'VARCHAR(20)'),SPACE(1),'') AS Price
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
        REPLACE(REPLACE(MealPlan,@euro,@separator), @separator, ']]></r><r><![CDATA[')   
        ']]></r></root>' AS XML)) AS t1(c);

Output

 ---- ------------------------------------------------------------ -------- 
| ID |                          MealPlan                          | Result |
 ---- ------------------------------------------------------------ -------- 
|  1 | Sansrepas315€/pers.=630€pour2pers.Devis/RésaSelectionner   |    630 |
|  2 | Sansrepas394€/pers.=787€pour2pers.Devis/RésaSelectionner   |    787 |
|  3 | Sansrepas547€/pers.=1 093€pour2pers.Devis/RésaSelectionner |   1093 |
|  4 | Sansrepas547€/pers.=1 093€pour2pers.Devis/RésaSelectionner |   1093 |
|  5 | Sansrepas700€/pers.=1 400€pour2pers.Devis/RésaSelectionner |   1400 |
|  6 | Sansrepas328€/pers.=656€pour2pers.Devis/RésaSelectionner   |    656 |
 ---- ------------------------------------------------------------ --------