-1

I am trying to remove a space and a no-break space from a varchar field i'm going to cast as an int, however, when using the replace method SQL Management Studio messes up the syntax after accepting it so I can't save it. This my query:

SELECT SZOMSCHRIJVING 
FROM dbo.BETONKWALITEITEN AS b 
WHERE (iBetonMengsel = CAST(REPLACE(p.SZBETONRECEPT, '  ', '') as INT))

After accepting this syntax, SQL Management Studio turns it into the following code:

SELECT SZOMSCHRIJVING 
FROM dbo.BETONKWALITEITEN AS b 
WHERE (iBetonMengsel = CAST(REPLACE('p.SZBETONRECEPT.   . ',,) AS INT))

How do I replace the space and no-break space from this column?

Image to attempt to show the problem. enter image description here

WhatsThePoint
  • 3,395
  • 8
  • 31
  • 53
Tinne
  • 1
  • 1
  • 3
    What do you mean "accepting the syntax"? I have never witnessed SSMS changing SQL on execution, so I'm not really sure what you mean here. – Thom A Mar 19 '18 at 08:54
  • 1
    Are you using any third-party plugins to format your code or something else of that kind? – Abhishek Mar 19 '18 at 09:00
  • I tried to visualise it as clear as possible https://i.imgur.com/7wGkP40.png – Tinne Mar 19 '18 at 09:04
  • I am using SQL management studio as it is, the database is however not made by us – Tinne Mar 19 '18 at 09:04
  • I cant see it being a problem with the database, I think it would be like what @Abhishek suggested with a plugin causing this issue – WhatsThePoint Mar 19 '18 at 09:11
  • Use the `CHAR()` function to replace specific characters. For example `CHAR(32)` is the `SPACE` character. – EzLo Mar 19 '18 at 09:11
  • 3
    That doesn't look like the Query Editor for SSMS where the query should be written and executed. And don't present 'Enter', select your query and either press F5 or hit the execute button – Abhishek Mar 19 '18 at 09:12
  • It's a query to edit a view table, it's not the query editor – Tinne Mar 19 '18 at 09:27
  • If you're editting a `VIEW` why not use an `ALTER` statement? I agree with @Abhishek that doesn't look like SSMS; and I think that's your problem. Use a Query Windows in SSMS. – Thom A Mar 19 '18 at 09:40
  • @Tinne, use a regular query window instead of the SSMS view editor. The view editor is buggy and will change your code. – Dan Guzman Mar 19 '18 at 09:45
  • Apparently it was indeed a bug in the SSMS view editor, works like a charm in the query editor below it (didn't think it was possible to edit something in that window at first). Thanks for the help everyone! – Tinne Mar 19 '18 at 09:54

1 Answers1

0

Try replacing the char values 32 for space and 160 for no-break space:

SELECT SZOMSCHRIJVING 
FROM dbo.BETONKWALITEITEN AS b 
WHERE (iBetonMengsel = CAST(REPLACE(REPLACE(p.SZBETONRECEPT, char(160), ''), char(32), '') as INT))
cloudsafe
  • 2,444
  • 1
  • 8
  • 24