Want to know why does it happen in Substring? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Want to know why does it happen in Substring?

Author

Topic

manibad
Starting Member

9 Posts

Posted - 2012-09-20 : 17:24:08

When i use the below query
IN temp.salary column i have a value like 120098.33,51234.54

select Substring(convert(varchar,temp.salary,102),-2,LEN(convert(varchar,temp.salary,102))) from temp

i end up like this

120,098
51,234

Whereas when i use the same query like this

select
Substring(convert(varchar,120098.33
,102)
,-2,
LEN(convert(varchar,120098.33
,102)))

120098

Why does it happen so?PLease explain me this.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-20 : 18:09:50

What is the datatype for Salary? Is it Money? If so, the select from TEMP is converting a MONEY to a VARCHAR. THe style makes no sense, so I guess that it is using a style of 1 instead of 0..? The second example is converting from a Float/Decimal to varchar. That has different rules that Money, hence the difference. As an example:

SELECT convert(varchar,'120098.33',102)
SELECT convert(varchar,120098.33,102)
SELECT convert(varchar,CAST(120098.33 AS MONEY),102)
SELECT convert(varchar,$120098.33,102)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources