Solved

SQL Server Polish Formatted Strings

Posted on 2010-11-16
6
594 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

630 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