Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server Polish Formatted Strings

Posted on 2010-11-16
6
Medium Priority
?
619 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

971 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