Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Polish Formatted Strings

Posted on 2010-11-16
6
Medium Priority
?
610 Views
Last Modified: 2012-05-10
I hvae  alot of decimal string formatted in the polish locale and SQL server is erroring everytimwe I try to cast as decimal.

here si an example 1 400,50

Any help would be appreciated.
0
Comment
Question by:ascendix
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Expert Comment

by:advapp
ID: 34150955
It wasn't clear but I believe you have the values as string/varchars?  So here's an example of how you might convert to decimal.

declare @strval varchar(50), @decval decimal(10,2)

set @strval = '1 400,50'
set @decval = convert(decimal(10,2),Replace(Replace(@strval,',','.'),' ',''))
select @strval as OldVal, @decval as DecVal
0
 

Author Comment

by:ascendix
ID: 34151334
the problem with this is that if I have a US formatted number is going to mess it up
 like 1,000.0
0
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34153668
Remove the comma at all as below:

set @strval = '1 400,50'
set @decval = convert(decimal(10,2),Replace(Replace(@strval,',',''),' ',''))
select @strval as OldVal, @decval as DecVal
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 34153762
please see this code example to "cast" dynamically based on your settings and the data:
declare @t table ( data varchar(100) )
insert into @t values('1 400,50')
insert into @t values('1,000.40')

-- find out dynamically what the decimal is, and what the thousand separator is
declare @d char(2)
set @d = replace(right('_' + replace( cast(1111.1 as varchar(10)), '1', '') ,2), '_', ' ')

select '<' + @d + '>'
, t.data
, case 
  when t.data like '%.%,%' then replace(replace(replace(data, '.', '@'), ',', right(@d,1)), '@', left(@d, 1))
  when t.data like '%,%.%' then replace(replace(replace(data, ',', '@'), '.', right(@d,1)), '@', left(@d, 1))
  when t.data like '% %,%' then replace(replace(replace(data, ' ', '@'), ',', right(@d,1)), '@', left(@d, 1))
  when t.data like '% %.%' then replace(replace(replace(data, '.', '@'), '.', right(@d,1)), '@', left(@d, 1)) 
  when t.data like '%,%' then replace(data, ',', right(@d,1))
  when t.data like '%.%' then replace(data, '.', right(@d,1))
  else t.data end new_data
, cast(replace(case 
  when t.data like '%.%,%' then replace(replace(replace(data, '.', '@'), ',', right(@d,1)), '@', left(@d, 1))
  when t.data like '%,%.%' then replace(replace(replace(data, ',', '@'), '.', right(@d,1)), '@', left(@d, 1))
  when t.data like '% %,%' then replace(replace(replace(data, ' ', '@'), ',', right(@d,1)), '@', left(@d, 1))
  when t.data like '% %.%' then replace(replace(replace(data, '.', '@'), '.', right(@d,1)), '@', left(@d, 1)) 
  when t.data like '%,%' then replace(data, ',', right(@d,1))
  when t.data like '%.%' then replace(data, '.', right(@d,1))
  else t.data end, ' ', '') as decimal(30,2) ) dec_data
from @t t

Open in new window

0
 
LVL 1

Expert Comment

by:advapp
ID: 34157446
Ah, the original message didn't indicate you had BOTH US and Polish.  Then, as indicated, just switch on by examining the format.  If the value has a space, then treat it as Polish and convert it.  If not, treat it as US and do no Replace(); just convert it to decimal.
0
 

Author Closing Comment

by:ascendix
ID: 34169636
Thanks a bunch this worked great
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question