Solved

# SQL Server Polish Formatted Strings

Posted on 2010-11-16
576 Views
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
Question by:ascendix

LVL 1

Expert Comment

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

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

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

LVL 142

Accepted Solution

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
``````
0

LVL 1

Expert Comment

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

ID: 34169636
Thanks a bunch this worked great
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…