[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

TSQL Help

Posted on 2011-10-13
8
Medium Priority
?
237 Views
Last Modified: 2012-05-12
I have an nvarchar field that looks like this
2220

I need to make it look like this
2,220

How?
0
Comment
Question by:cheryl9063
  • 3
  • 3
  • 2
8 Comments
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 36963758
Does the data going into that field need to be formatted like that or the data pulled from that field in an output query need to be formatted like that?
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 36963759
I need to update the data in the table to that
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36963857

SELECT CONVERT(varchar, CAST(987654321 AS money), 1)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:cheryl9063
ID: 36963871
That does not give me 2,220
0
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 36963877
create function make_number (@value varchar(40))
returns varchar(40)
as
begin
declare @o varchar(40)
set @o = ''
 while len(@value) > 3
  begin
   set @o = ',' + right(@value, 3) + @o
   set @value = substring(@value, 1, len(@value) - 3)
  end
return @value + @o
end
go
update table_name set field_name = dbo.make_number(field_name)
go
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 36963886
I need another way other that creating a function.. How about where len(field)=4? Would that work
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36963909
SELECT CONVERT(varchar, CAST(2220 AS money), 1)
--2,220.00
SELECT substring(CONVERT(varchar, CAST(2220 AS money), 1),0, charindex('.',CONVERT(varchar, CAST(2220 AS money), 1)))
--2,220
0
 
LVL 8

Accepted Solution

by:
Crashman earned 2000 total points
ID: 36963934
the one you like best

SELECT CONVERT(varchar, CAST(2220 AS money), 1)
--2,220.00
SELECT substring(CONVERT(varchar, CAST(2220 AS money), 1),0, charindex('.',CONVERT(varchar, CAST(2220 AS money), 1)))
--2,220
SELECT LEFT(CONVERT(varchar, CAST(2220 AS money), 1),charindex('.',CONVERT(varchar, CAST(2220 AS money), 1))-1)
--2,220
SELECT LEFT(CONVERT(varchar, CAST(2220 AS money), 1),LEN(CONVERT(varchar, CAST(2220 AS money), 1)) -3)
--2,220
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 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