REPLACE statement issue

Transact-SQL
1

When I run the following SQL
UPDATE providers SET PracticeAddress2 = REPLACE(PracticeAddress2, 'Street', 'St')

None of the data has changed. My goal is to convert 3091 Main Street to 3091 Main St

Can anyone help me with this?

2

Is the colum PracticeAddress2 where the original data is located? Or is it another column?

This works for me

DECLARE @TableExample TABLE
(
	Value VARCHAR(64) NOT NULL
);

INSERT INTO @TableExample (Value) 
SELECT '123 Elm Street' UNION ALL
SELECT '456 Main Street' UNION ALL
SELECT '789 State Street';

SELECT *
  FROM @TableExample;

UPDATE @TableExample
  SET Value = REPLACE(Value, 'Street', 'St');

SELECT *
  FROM @TableExample;
3

Is the instance case sensitive? Because otherwise the statement looks fine, other than not including a WHERE condition for efficiency:

UPDATE providers
SET PracticeAddress2 = REPLACE(PracticeAddress2, 'Street', 'St')
WHERE PracticeAddress2 LIKE '%Street%'

4

I'm sorry, yes the column is named PracticeAddress2
No, it is not case sensitive.

Thank you!

5

No, it is not case sensitive.

6

Run these two statements and see if they return any rows at all.

SELECT * FROM providers 
WHERE PracticeAddress2 LIKE '%Street%'

SELECT * FROM providers 
WHERE PracticeAddress2 COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%Street%'
7

You also need to verify the database collation setting and the column collation setting. In SQL Server, you can set each column's case sensitivity separate from other columns.