String into Numeric

[Ladyazh]

I have value that I've got by extracting portion of the field. Now i need to link this field to the table where these values are Numeric. However extracted are strings.
How do i write function to convert Str into Num?
Thanks

 

[gmmastros]

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]Int[/color], ParseName([COLOR=#FF00FF]Replace[/color](APPCD, [COLOR=red]'='[/color], [COLOR=red]'.'[/color]), 1))
[COLOR=blue]From[/color]   [!]TableName[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ladyazh]

Thanks but why do I see '.' replacing '='? Just asking. Thanks again

 

[Ladyazh]

gmmastros,
you were right, I was wrong and I hope you will have a strenght to find it in your heart to fogive me.
Thanks so much

 

[SQLSister]

If you are asking about how to join the two fields togethr then you can use the cast or convert on one side of the join to make it match the opther side of the join. Be wawre though that this is likely to be a slow porcess and you might be far better off to extract the numeric part of the string into a separate numeric field that is filled inat time of innsert or update through a calculated field or trigger (depending onthe complexity of how you extract the number). If this is just a one time thing, use cast or convert in your join, otherwise consider changing the structure to avoid unnecessary repeat processing.

Questions about posting. See faq183-874

 

[gmmastros]

Ok. Don't worry about it.

Let me explain. The ParseName function was written by microsoft to parse object names. When you use a linked server, for example, you need to use the 4 part naming convention. Ex:

Select * From Server.Database.Owner.Table

Internally, microsoft uses the ParseName function to extract the info. So, for example...

[COLOR=blue]Declare[/color] @Data [COLOR=blue]VarChar[/color](100)

[COLOR=blue]Set[/color] @Data = [COLOR=red]'Server.Database.Owner.Table'[/color]

[COLOR=blue]Select[/color] ParseName(@Data, 4), 
       ParseName(@Data, 3), 
       ParseName(@Data, 2), 
       ParseName(@Data, 1)

The problem is that ParseName looks for the period (.) synbol to parse. In your data, you have an equal (=) symbol seperating your data. Therefore, in order to get the ParseName function to work, I had to replace + with .

Since this is a select statement, the data in your table is not effected.

Does this make sense now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom