display 3 digits before decimal and after decimal point 6dig

raviteja sridasyam

10y981

How to Display three digits before decimal and after decimal point six digits in SQL Server

I had column called longitude and latitude with data type like "nvarchar". when user can enter something like (23.23424432) ,(432423.343) this for should not take only this format should accept.(232.343244) please can can any help me how 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)

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

raviteja sridasyam10y ago
In Sql below code is for GPS_LATITUDE and GPS_LATITUDE column i need to add validation for this.

CODE :

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
0
ali tuncer10y ago
Do you have to do this in SQL Server?
Can you validate in UI? (Web,Winform..), send it to sql server if it is validated..I think it will be easier this way..

0