Removing First Character from SQL Column

[YorkeG]

Hello,

I am trying to run a query from a production database onto a dashboard in the office. My dashboard does not allow special characters such as dollar signs, so i need to remove these special characters within my SQL statement.

Currently my SQL statement looks like this:
SELECT SO_Header.Sales_Order, Customer.Sales_Rep, SO_Header.Total_Price, SO_Header.Customer
FROM PRODUCTION.dbo.Customer Customer, PRODUCTION.dbo.SO_Header SO_Header
WHERE SO_Header.Customer = Customer.Customer AND ((SO_Header.Order_Date=dateadd(dd,0,datediff(dd,0,getdate()))))

The column where the special characters are present is (SO_Header.Total_Price)

Can someone help me how i would modify this statement to remove the first/special characters from this column?

Please let me know.

Regards,
Yorke

[Norie]

Yorke

What type of database are you running this against?

[YorkeG]

It is called JobBOSS...i believe it is a generic ODBC database?
Would that make sense?

[Norie]

You could try SUBSTRING - SUBSTRING(string_exp , start, length)

SUBSTRING(SO_Header.Total_Price, 2, 10000)

Note the 10000 is just an arbritrary number for the length.

[YorkeG]

okay...excuse the ignorance, where would this fit into the SQL statement?

appreciate your help by the way!

[Norie]

It would replace SO_Header.Total_Price.

By the way that, if it works of course, will only remove the first character.

If you have other 'special' characters elsewhere in the field you would need something like REPLACE(string_exp1, string_exp2, string_exp3).

[YorkeG]

Thanks Norie,

I did that, now i am getting another error message. Please see attached screenshot.

[Norie]

That must mean Total_Price is some sort of numeric field.

What are the 'special' characters you want to remove?

Are they currency symbols?

[YorkeG]

Okay.

Yes, i believe it is a currency formatted field. with currency symbols.

[Norie]

To deal with this using SQL you'll need to convert the values in the field to text and then apply the functions I've suggested.

That could become quite complicated.

Is there no way you can do the import then handle the data in Excel?

[YorkeG]

okay. yes that sounds complicated.

I can handle the import into excel without a problem, but the difficulty is getting excel to sync with the dashboard software

Is there any way that when an excel workbook is closed it will still run an auto refresh every five minutes or so?
The problem is i need to set the excel workbook up as a data source for my dashboard, so essentially excel will pull the info out of
the production database, then pass it onto the dashboard software. I have set my excel workbook to refresh into the dashboard on 2
minute intervals, but i can't get the excel workbook to pull data from the production database on automatic refresh when the workbook is closed.
DO you have any idea how i could do this?

Yorke

[Kyle123]

Why not just cast it to a decimal?

CONVERT(DECIMAL(18,6), SO_Header.Total_Price)

å

[YorkeG]

thanks a lot for the suggestion - but this doesn't seem to work either
i am beginning to think there is something more underlying this issue.
Has anyone worked with iDashboards before?