[SOLVED] How do you add decimals to a nvarchar data type for a SQL table? Table has letters and numbers which is why it's nvarchar type

How do you add decimals to a nvarchar data type for a SQL table? Table has letters and numbers which is why it's nvarchar type

Table 1 has a column diagnosis_codes and the codes have numbers, letters, and decimals. Example N92.6 or Z32.01.

Table 2 has a column codes, imported from an Excel file, and the format of the codes didn't have decimal points for this table. EXAMPLE N926 & Z3201 - same codes from Table 1 minus the decimal point.

I want to be able to add decimals, (i think decimal 10,2) to Table 2 column Codes that way when I do any join script to compare Table 1 column diagnosis_codes to Table 2 column codes they would match.

Solution

A combination of string functions can able to achieve this requirement.

assuming these are your column values:

declare @var1 varchar(30) = 'N92.6'
declare @var2 varchar(30) = 'N926'

This query will force @var2 to format same with @var1. So you can use this on your where clause.

select concat(substring(@var2, 0, charindex('.', @var1)), '.', substring(@var2, charindex('.', @var1), len(@var1)-charindex('.', @var1)+1))

updating the actual data on the table, you can just replace dot(.) with empty string

update table1 set column1 = replace(column1, '.', '')