Validate three digits before decimal and after decimal point six digits

I have columns called longitude and latitude with nvarchar data type. The user can enter something like (23.23424432),(432423.343), which should not be allowed.

Can anyone help me to validate this in SQL Server?

• Data that can be entered by user: Ex: 0, 12, 101.0, 111.022222

• Data that can’t be entered by user: Ex: 1111, 111.2222222, 1234.1111

It should accept only this format: (232.343244)

Below code is accepting any numbers:

+ CASE WHEN (CAST(A.GPS_LATITUDE AS NVARCHAR) IS NOT NULL AND CAST(A.GPS_LATITUDE AS NVARCHAR) <>'' AND CAST(A.GPS_LATITUDE AS NVARCHAR) NOT LIKE'%*%') 
AND ISNUMERIC(CAST(A.GPS_LATITUDE AS NVARCHAR)+'e0') = 0 OR (LEN(A.GPS_LATITUDE)>20)
 THEN 'Invalid Latitude. ' ELSE '' END
+ CASE WHEN (CAST(A.GPS_LONGITUDE AS NVARCHAR) IS NOT NULL AND CAST(A.GPS_LONGITUDE AS NVARCHAR) <>'' AND CAST(A.GPS_LONGITUDE AS NVARCHAR) NOT LIKE'%*%')
AND ISNUMERIC(CAST(A.GPS_LONGITUDE AS NVARCHAR)+'e0') = 0 OR (LEN(A.GPS_LONGITUDE)>20)
 THEN 'Invalid Longitude. ' ELSE '' END

In my application we had a download template button. When we press the download template button, it downloads a template with only a header - with columns like LONGITUDE and LATITUDE. We need to enter manually like 234.234325 but user can enter 2342.234242 or 4353453.123213 and save the Excel file.

In the application UI, there will be an upload button. Pressing the upload button sends the data to the database and it validates in a stored procedure. In the stored procedure, the above lines of code validate the data. We need to add decimal number validation like (432.342342) for above code.

-
You're better off using a DECIMAL(9,6) data type in the database.

• And then you've validate with a simple numeric BETWEEN

-
However, if you insist on a character type, use CHAR instead of NCHAR - you're only accepting periods and numbers, so there's never any "Unicode"; cut your space use in half.

-
And then you could validate with something like

a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9]' ESCAPE '~'


-
Note that doing this with character types is both annoying and not nearly as performant.

Note that the leftmost digit is limited to 0-3, since you'd never have a latitude or longitude starting with a 4 or higher (I'm assuming degrees).

Since you're on SQL Server 2008 R2, you may also want to read the Stackoverflow question Why use the SQL Server 2008 geography data type?. A very brief summary is: DECIMAL(9,6) is significantly smaller, and for a simple SELECT Lat, Long is significantly faster. However, to do accurate proximity searches (which is often why coordinates are stored), distance calculations (also often why you need them), or areas and the like, the geography type is much simpler and almost certainly more accurate.

StackExchange Database Administrators Q#129769, answer score: 8