Solved

SQL Server Polish Formatted Strings

Posted on 2010-11-16
6
572 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
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 142

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now