Truncate Lat/Long data

Question

Hello,

I am trying to truncate lat/long data. Here's my scenario:

These are numeric (10,8) data types first of all.

if my lat is 27.41317320 then I need to drop the zero to only return 27.4131732

Now, if my lat is this then I need to return the entire lat: 27.41317302

And if my lat is 27.41317300 then return 27.413173

I tried this:

Answer 1

I got it to work. Added a Data Conversion transform from the source OLEDB to csv flat file destination. Problem solved.

Answer 2

If you want to display the data without the trailing zeroes, then try something like this:

select format(MyColumn, '0.########') as lat from MyTable

Answer 3

pituach, thanks for the info.

I changed the column to text in the csv file I send to the vendor and it did not display the trailing zero.

As far as the vendor is concerned, they are importing the csv file in to an Oracle database. I have a job that generates a csv file daily. The vendor's requirement for Lat/Lon is to have 8 digits to right of the decimal. Ok, no problem. So I send them this Lat:

27.41317320

The vendor sends back a csv response file, basically comparing what we send and what they have. And in their response file they say records don't match

E.G What I send 27.41317320 and what they send back 27.4131732

This causes the records to be out of "sync". Like I said before, I have a SSIS package that works great. I went this route because I have to upload the csv file to their sFTP site daily. I have a SQL job agent that runs the package around 9am daily Monday - Friday. And yes, granted I could use another technology to perform this task such as C# but truly, the SSIS was easiest, fastest to get the job done.

I hope this all makes sense!

Thanks!

Answer 4

Good day,

When opening the csv file in Excel, the trailing zeros are not there. But... when opening the csv file, say using notepad the zeros ARE there.

You confuse a displaying format with actual data format. When you open the file in Excel then the Excel see it as decimal type and therefore it does not display the trailing zero since 0.10 is actually 0.1 when speaking about numbers. If you change the display type in the Excel to text instead of general then it will probably show you the trailing zero.

I have to upload the csv file to a vendor and they tell me that the Lat/Long don't match to what they have.

It could help if you provide more information about the vendor requests of the format since in theory trailing zero are allowed in most languages when dealing with geography data type

In any case, if the csv uses a format that does not fit your vendor and if it is up to you to provide a file without the trailing zero then you will need to create a new file.

If you will explain how you created this file then maybe we can help you create a file that fit the recruitment from the start.

If you want to fix this file after it was created then this is not a job for SQL Server. You should use PowerShell, C# or other technologies and languages that support parsing text better.

In general from the SQL Server perspective, you can use the function FORMAT as @Viorel already explain. This is something that can help you when you create the file. Don't save the trailing zero but create a file without them.

Answer 5

Thank you Viorel-1. This does work, But I have some funkiness. I have a SQL code in an SSIS dataflow and it out puts it to a csv file. When opening the csv file in Excel, the trailing zeros are not there. But... when opening the csv file, say using notepad the zeros ARE there. I have to upload the csv file to a vendor and they tell me that the Lat/Long don't match to what they have. Frustrating to say the least.