I have a field that should contain 6 digits, a period, and six digits (######.######). The application that I use allows this to be free-form entry. Because users are users and will do what they want I have several fields that have a dash and some letters afterwards (######.######-XYZ).
Using T-SQL how do I identify and subsequently remove the -XYZ so that I can return the integrity of the data. The column is an NVARCHAR(36), PK, and does not allow null values. The column in question does have a unique columnID field.
Advertisement
Answer
since it’a free-form and “users are users”, use charindex to find out if 1) there is a - and 2) remove it.
Example:
DECLARE @test NVARCHAR(36) = N'######.######-XYZ'
SELECT SUBSTRING(@test,1,COALESCE(NULLIF(CHARINDEX('-',@test,1),0),LEN(@test)+1)-1)