Hi everyone
I have a table that has close to 30 columns. One of the columns UNDERLYING_SYMBOL needs to be updated because the raw data file has "^" in the name. I need to remove the "^" from the name. For example, "^SPX" should be "SPX". I created below query. However, the column UNDERLYING_SYMBOL has 0 showing up and not the name. What am I doing wrong?
update dbo.MainTable
set UNDERLYING_SYMBOL = CHARINDEX('^',RIGHT(UNDERLYING_SYMBOL, LEN(UNDERLYING_SYMBOL) - CHARINDEX('^', UNDERLYING_SYMBOL)))
where charindex('^', underlying_symbol) = 1
Thank you
update dbo.MainTable
set UNDERLYING_SYMBOL = STUFF(UNDERLYING_SYMBOL, 1, 1, '')
where underlying_symbol LIKE '^%'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 12, 2023 at 9:12 pm
The problem in your original statement is that your calc -- CHARINDEX('^',RIGHT(UNDERLYING_SYMBOL, LEN(UNDERLYING_SYMBOL) - CHARINDEX('^', UNDERLYING_SYMBOL))) -- involves only numeric values, so the result is numeric. Since it's being assigned to a char column, SQL implicitly changes the numeric result to char.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 12, 2023 at 9:16 pm
The problem in your original statement is that your calc -- CHARINDEX('^',RIGHT(UNDERLYING_SYMBOL, LEN(UNDERLYING_SYMBOL) - CHARINDEX('^', UNDERLYING_SYMBOL))) -- involves only numeric values, so the result is numeric. Since it's being assigned to a char column, SQL implicitly changes the numeric result to char.
you are absolutely right. i missed that completely. thank you so much for your help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply