Export (0) Print
Expand All


Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Replaces existing text with new text in a text string.

SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)




The text in which you want to substitute characters, or a reference to a column containing text.


The existing text that you want to replace.


The text you want to replace old_text with.


(optional) The occurrence of old_text you want to replace. If omitted, every instance of old_text is replaced

Use the SUBSTITUTE function when you want to replace specific text in a text string; use the REPLACE function when you want to replace any text of variable length that occurs in a specific location in a text string.

The SUBSTITUTE function is case-sensitive. If case does not match between text and old_text, SUBSTITUTE will not replace the text.

This DAX function may return different results when used in a model that is deployed and then queried in DirectQuery mode. For more information about semantic differences in DirectQuery mode, see  http://go.microsoft.com/fwlink/?LinkId=219171.


The following formula creates a copy of the column [Product Code] that substitutes the new product code NW for the old product code PA wherever it occurs in the column.


=SUBSTITUTE([Product Code], "NW", "PA") 
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft