SQL Server String TRANSLATE() Function

SQL Server TRANSLATE() Function

The SQL Server TRANSLATE() function is used to replace a sequence of characters in a string with another sequence of characters. This function is useful for substituting multiple characters in a single operation.

Syntax

SELECT TRANSLATE(string, from_string, to_string);

The TRANSLATE() function takes three arguments:

  • string: The string to be modified.
  • from_string: The sequence of characters to be replaced.
  • to_string: The sequence of characters to replace the from_string.

Example SQL Server TRANSLATE() Function Queries

Let's look at some examples of SQL Server TRANSLATE() function queries:

1. Basic TRANSLATE() Example

SELECT TRANSLATE('abcdef', 'abc', '123') AS result;

This query replaces the characters 'a', 'b', and 'c' with '1', '2', and '3' respectively in the string 'abcdef'. The result will be:

result
------
123def

2. TRANSLATE() with Multiple Replacements

SELECT TRANSLATE('hello world', 'hld', 'HLD') AS result;

This query replaces the characters 'h', 'l', and 'd' with 'H', 'L', and 'D' respectively in the string 'hello world'. The result will be:

result
------
HeLLo worLD

3. TRANSLATE() with a Column

SELECT product_name, TRANSLATE(product_name, 'aeiou', '12345') AS modified_name
FROM products;

This query replaces the vowels 'a', 'e', 'i', 'o', and 'u' with '1', '2', '3', '4', and '5' respectively in the product_name column for each product. The result will show the product_name and the modified version as modified_name.

4. TRANSLATE() with a Variable

DECLARE @myString VARCHAR(50);
SET @myString = 'Data Science';
SELECT TRANSLATE(@myString, 'aei', '123') AS result;

This query uses a variable to store a string and then replaces 'a', 'e', and 'i' with '1', '2', and '3'. The result will be:

result
-------------
D1t2 Sc2nce

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the TRANSLATE() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    description VARCHAR(50)
);

In this example, we create a table named example_table with columns for id and description.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, description) VALUES (1, 'Apple Pie');
INSERT INTO example_table (id, description) VALUES (2, 'Banana Bread');
INSERT INTO example_table (id, description) VALUES (3, 'Cherry Tart');

Here, we insert data into the example_table.

Step 3: Using the TRANSLATE() Function

This step involves using the TRANSLATE() function to modify the description column.

SELECT id, description, TRANSLATE(description, 'aeiou', '12345') AS modified_description
FROM example_table;

This query retrieves the id, description, and the modified version of the description column for each row in the example_table. The result will be:

id  description   modified_description
--- ------------- --------------------
1   Apple Pie     1ppl2 P32
2   Banana Bread  B1n1n1 Br21d
3   Cherry Tart   Ch2rry T1rt

Conclusion

The SQL Server TRANSLATE() function is a powerful tool for replacing a sequence of characters in a string with another sequence of characters. Understanding how to use the TRANSLATE() function and its syntax is essential for effective string manipulation and data processing in SQL Server.