Alternative to Substring_Index in SQL Server

Question

I wrote the below query to create 2 new columns and then add the data data from column Level_code before the first "-" into column BU and after the "-" into column Site_name. The issue is that in azure data studio there is no Substring_index function. Is there an alternative to this that does the same feature?

--Add two new columns to hold the split values

ALTER TABLE Table_Name
ADD "BU" VARCHAR(255),
ADD "Site_Name" VARCHAR(255);

--Update the new columns with the split values

UPDATE your_table_name 
SET BU = SUBSTRING_INDEX(Level_code, '-',1), --Get the part before the first "-" 
Site_Name = SUBSTRING_INDEX(Level_code, '-',-1); --Get the part after the first "-"

`Substring_index` isn't a function _in SQL Server_. It doesn't matter if you're using Azure Data Studio or any other tool. I would also suggest using a computed column for this, then you don't have to run an update (and keep running updates constantly every time any row is changed or added). — Aaron Bertrand, Jul 21 '23 at 14:06
Read about substring function and charindex function. A combination of them should be able to split the field as you want — siggemannen, Jul 21 '23 at 14:09
Does this answer your question? [SQL Server equivalent of substring\_index function in MySQL](https://stackoverflow.com/questions/23854724/sql-server-equivalent-of-substring-index-function-in-mysql) — Thom A, Jul 21 '23 at 14:13
A better design would be to store `BU` and `Site_Name`, so they can be queried and indexed, and construct `Level_code` either in SELECT queries or as a computed column — Panagiotis Kanavos, Jul 21 '23 at 14:17

score 0 · Accepted Answer · answered Jul 23 '23 at 16:11

An interesting solution is to use persistant computed columns like :

ALTER TABLE Table_Name
   ADD BU AS LEFT(Level_code, CHARINDEX('-', Level_code) - 1) PERSISTED,
       Site_Name AS RIGHT(Level_code, LEN(Level_code) - CHARINDEX('-', Level_code) - 1) PERSISTED;

But the good way would be to have a normalized design of your database. In fact, your demand show that you violate the First Normal Form by combining two information in one column...

It will be far better to have those two columns into your table design and create a view that concatenate those columns with a "-" for your "level_code". In addition this solution avoid to store the character "-"... (one octet less over all the rows...).

Alternative to Substring_Index in SQL Server

1 Answers1