Validate three digits before decimal and after decimal point six digits

Question

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.

Answer 1

• 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.

Answer 2

To answer the actual question, the built-in function LEN() will give the total number of characters in a string and CHARINDEX() will return the position of any given substring. In your case, with a required format of three-dot-six, LEN() must return "10" and CHARINDEX('.', value) must return "4" to have a valid input. Putting it together you get

declare @t table (value nvarchar(99));

insert @t values
    ('23.23424432'),
    ('432423.343'),
    ('232.343244'),
    ('99'),
    (NULL);

select
    value,
    case
        when value is NULL then 'Null is valid'
        when (LEN(value) = 10) and (CHARINDEX('.', value) = 4) then 'Valid'
        else 'Wrong'
    end 
from @t;

Remember, too, that latitude and longitude are bounded by a sphere, so are limited to +/- 180 (long) and +/- 90 (lat).

Once you know the decimal point's position you can use SUBSTRING() to separate whole degrees from the fractional part and validate. ISNUMERIC() will be useful. Ranges can be checked using COVERT() to make a float for the whole value or an integer for each part.

I know you may be constrained by existing or external systems but the data type is wrong. SQL Server supports specific geography types specifically for this sort of thing. They are based on floating point types, which would have been a reasonable second choice.

Answer 3

It should accept only this format: (232.343244)

I think this specification is confusing the issue. It isn't really a format that shows variation. I believe you meant something like either ###.###### or even XXX.YYYYYY. If you are accepting Latitude and Longitude values, then those have valid ranges, and valid values can have single or double digits to the left of the decimal, just as you show in the "Data that can be entered by user:" examples. And, they can be positive or negative, so you need to allow for an optional preceding minus sign (-), so maybe either [-]###.###### or [-]XXX.YYYYYY. So to describe this we would say: "up to 3 digits to the left of the decimal, and up to 6 digits to the right of the decimal."

Also, there is not much benefit to validating the number of digits to the right of the decimal since that is merely an issue of precision and can be rounded up or truncated. Please see this answer for details: What is the maximum length of latitude and longitude?

The following code validates the actual valid ranges for both types of values (since they have different ranges -- why allow 3 digits to the left of the decimal for Latitude when it won't ever use more than 2 digits?).

Also, I was not sure what to make of your AND CAST(A.GPS_LONGITUDE AS NVARCHAR) NOT LIKE'%*%') condition, but I duplicated it here. It can be changed easily enough if the intention was to skip values that consist of only one or more asterisks. Lastly, if there was a specific purpose in casting the NVARCHAR field to an NVARCHAR then it should be stated in the Question (and if so, you should specify a max length, as in NVARCHAR(30) rather than just NVARCHAR).

SET NOCOUNT ON;
DECLARE @TestValues TABLE (GPS_LATITUDE NVARCHAR(50), GPS_LONGITUDE NVARCHAR(50));
INSERT INTO @TestValues (GPS_LATITUDE, GPS_LONGITUDE) VALUES
(N'20.0', N'20.212'),
(N'-20.0', N'-20.212'),
(N'91.0', N'20.212'),
(N'-91.0', N'20.212'),
(N'41.0-', N'200.212'),
(N'-41.0', N'200.212'),
(N'-2*0.0', N'-20.212'),
(N'-20.0', N'-2*0.212'),
(N'2-0.0', N'2-0.212'),
(N'20.123456789', N'20.987654321'),
(N'90.000001', N'180.000001'),
(N'12.345.678', N'87.654.321'),
(NULL, NULL),
(N'', N''),
(N'34.2a5', N'45.6b923');

SELECT tmp.GPS_LATITUDE,
       tmp.GPS_LONGITUDE,
       CASE
           WHEN tmp.GPS_LATITUDE LIKE N'%[^.0-9*-]%' OR LEN(tmp.GPS_LATITUDE) > 11
                THEN 'Invalid Latitude (bad character OR too long). '
           WHEN tmp.GPS_LATITUDE NOT LIKE N'%*%' AND ISNUMERIC(tmp.GPS_LATITUDE) = 0
                      AND LEN(tmp.GPS_LATITUDE) > 0
                THEN 'Invalid Latitude (not a number). '
           WHEN ISNUMERIC(tmp.GPS_LATITUDE) = 1
                    AND CONVERT(FLOAT, tmp.GPS_LATITUDE) NOT BETWEEN -90.0 AND 90.0
                THEN 'Invalid Latitude (range outside of -90.0 to 90.0). '
           ELSE ''
      END +
       CASE
           WHEN tmp.GPS_LONGITUDE LIKE N'%[^.0-9*-]%' OR LEN(tmp.GPS_LONGITUDE) > 11
                THEN 'Invalid Longitude (bad character OR too long). '
           WHEN tmp.GPS_LONGITUDE NOT LIKE N'%*%' AND ISNUMERIC(tmp.GPS_LONGITUDE) = 0
                    AND LEN(tmp.GPS_LONGITUDE) > 0
                THEN 'Invalid Longitude (not a number). '
           WHEN ISNUMERIC(tmp.GPS_LONGITUDE) = 1
                   AND CONVERT(FLOAT, tmp.GPS_LONGITUDE) NOT BETWEEN -180.0 AND 180.0
                THEN 'Invalid Longitude (range outside of -180.0 to 180.0). '
           ELSE ''
      END AS [Validation]
FROM   @TestValues tmp;

Returns:

LATITUDE    LONGITUDE     Validation
--------    ---------     ----------
20.0        20.212
-20.0       -20.212
91.0        20.212        Invalid Latitude (range outside of -90.0 to 90.0). 
-91.0       20.212        Invalid Latitude (range outside of -90.0 to 90.0). 
41.0-       200.212       Invalid Latitude (not a number). Invalid Longitude (range outside of -180.0 to 180.0). 
-41.0       200.212       Invalid Longitude (range outside of -180.0 to 180.0). 
-2*0.0      -20.212
-20.0       -2*0.212
2-0.0       2-0.212       Invalid Latitude (not a number). Invalid Longitude (not a number). 
20.123456789   20.987654321   Invalid Latitude (bad character OR too long). Invalid Longitude (bad character OR too long). 
90.000001   180.000001    Invalid Latitude (range outside of -90.0 to 90.0). Invalid Longitude (range outside of -180.0 to 180.0). 
12.345.678  87.654.321    Invalid Latitude (not a number). Invalid Longitude (not a number). 
NULL        NULL    

34.2a5      45.6b923      Invalid Latitude (bad character OR too long). Invalid Longitude (bad character OR too long). 

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.

Since you have the opportunity to trap the data before it hits the database, you really would be better off doing this validation in the app code as it is much easier (and more efficient) to do that.

Answer 4

Take the provided value. Cast it to FLOAT and to DECIMAL(9,6). Take ABS() of the difference, and compare it to some epsilon acceptable difference, perhaps 0.0000005. If it's less than that, the input can be represented as DECIMAL(9,6), which is probably the datatype you should use in your table.

abs( cast(@input as float) - cast(@input as decimal(9,6) ) < 0.0000005

The above rule can be used as a CHECK constraint in your table, too.

HTH.