I have two tables with the following information :

*Products*

```
Name  |    ID   |   

```

Name contains a short string indicating the name, and language of the row.

*Languages*

```
Language   |   ID   |

```

Language would be the same substring contained within the Name column above.

What I would like to do is add a column to my Products table called LanguageID. 

I would then extract the substring indicating the language from the Name column, and do a case-insensitive comparison to the Language column in the Languages table. Where a match is found, I would like to insert the ID value from the Languages table into the newly created LanguageID column, creating a foreign key relationship.

So, for example in my Products Table, I have :

```
Name                  |   ID
Product 1 - enGlIsh   |   1

```

In my Languages table, I have :

```
Language    |   ID
English     |   77

```

So what I would like to end up with in my Products table is :

```
Name                   |   ID   |   LanguageID
Product 1 - enGlIsh    |   1    |    77

```

I have written a regEx to return the Language from my substring, I am not sure how I can use it and structure this query in SQL server, however. How can I do this ?

Here is the Regex :

```
Regex regEx = new Regex(@"(.+\s*-\s*.+\s*-\s*.+)\s*-\s*(\w{1,3}\s*-\s*\w{1,3})\s*-\s*.+");
                string language = regEx.Match(   NAME   ).Groups[2].Value.ToString(); 

```