converting special charectors to positive or negative numbers

Question

Post reply

converting special charectors to positive or negative numbers

kutemom09

SSC-Addicted

Points: 463
More actions
December 5, 2011 at 12:18 pm

#253021

I’m dealing with a situation where I import data from file. many columns contains negative or positive numbers but in the form of charectors. I would like to convert all values to be positive values or negative value based on the . what approach can I follow.Any help would be appreciated
FOR EXAMPLE
totalprice_due wholesale price
ØØØ1Ø99I ØØØ72976(
ØØØØ1Ø3J ØØØ82986)
in the above data
J represents as negative number 1
I represents as positive number 9
( represents as positive number 0
) represents as negative number 0
For example:
ØØØ72976( = $7297.6Ø (positive)
ØØØØ1Ø3J = $1Ø.31 (negative)
ØØØ1Ø99I = $1Ø9.99 (positive)
how can I convert them .Please I really need this favor.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

:blink:

To confirm, you're in SQL 2000? Also, what is your current import mechanism? DTS, BCP, Bulk?

EDIT: I removed a number of confusing items I wrote. I really, really, really need to learn to read. It's not like it wasn't right there in front of my face.

Nice work Sean.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Just building on your example, and assuming you don't have to do this directly on import.

create table #Vals(SomeValue nvarchar(15))

insert #Vals

select 'ØØØ1Ø99I' union all

select 'ØØØ72976(' union all

select 'ØØØØ1Ø3J' union all

select 'ØØØ82986)'

go

select * from #Vals

update #Vals set SomeValue = REPLACE(SomeValue, 'Ø', '0')

update #Vals set SomeValue = REPLACE(SomeValue, 'J', '1[]')

update #Vals set SomeValue = REPLACE(SomeValue, 'I', '9')

update #Vals set SomeValue = REPLACE(SomeValue, '(', '0')

update #Vals set SomeValue = REPLACE(SomeValue, ')', '0[]')

update #Vals set somevalue = case when CHARINDEX('[]', somevalue) > 0 then '-' + somevalue else somevalue end

update #Vals set SomeValue = REPLACE(SomeValue, '[]', '')

select CAST(Somevalue as numeric(9,2)) / 100

from #Vals

drop table #Vals

That is incredibly ugly but does work for the sample data you provided.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kutemom09 SSC-Addicted Points: 463 More actions · Answer 3

Thank you for the reply. I have imported the data from a text file to a table through business objects Data integrator .now I need to write a stored procedure to Insert this data into another table and convert all these charectors to appropriate numbers .as I am a newbie to SQL Server please bear with me if I am unable to explain the issue.

kutemom09 SSC-Addicted Points: 463 More actions · Answer 4

Thank you Sean I will try this and see.I appreciate your help.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

So the series of updates I posted should work. Just put code similar to that in your new proc. Update the holding table until you have it all cleaned up, then insert to your destination. Finally clear out the holding table. Or even better, pull your data into a temp table, massage it, insert into final table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Write a scalar function implementing the conversion rules.

Something like this:

SET @Result = convert(numeric, 18,2), -- change to the data type you actually need in your target table

CASE

WHEN RIGHT(@InputStr, 1) = 'I' THEN SUBSTRING(@InputStr, 1, LEN(@InputStr)-1 ) + '9'

WHEN RIGHT(@InputStr, 1) = '(' THEN SUBSTRING(@InputStr, 1, LEN(@InputStr)-1 ) + '0'

WHEN RIGHT(@InputStr, 1) = ')' THEN '-' + SUBSTRING(@InputStr, 1, LEN(@InputStr)-1 ) + '0'

WHEN RIGHT(@InputStr, 1) = 'J' THEN '-' + SUBSTRING(@InputStr, 1, LEN(@InputStr)-1 ) + '1'

ELSE @InputStr

END

Then use this function for conversion when copying data from old table to new one.

_____________
Code for TallyGenerator

ACinAZ SSCommitted Points: 1849 More actions · Answer 7

Was this a one-time import through DI? If not, why not use the data quality transform in DI to do the manipulation you need as it's being moved into the database? Yes, you can do the update after-the-fact, but I think it would make more sense, especially if you will be moving the data more than once, to do it in the ETL tool. Check out the Validation Transform to split the data based on the format ("I", "J", "(", ")") or maybe the Data Quality bits to see if they'll do what you need.

Edit: If you aren't visiting BOB on a regular basis, then you're missing out.

kutemom09 SSC-Addicted Points: 463 More actions · Answer 8

Hi This is a Bi weekly import.But I dont know how to do in Data integrator.Can you please explain how can I do it.The person who knows DI has resigned all of a sudden and none of us in the teams knows about it.Any help would be appreciated.

Thank you

ACinAZ SSCommitted Points: 1849 More actions · Answer 9

You'll have to dig a bit then. Sign up on BOB and start reading the BODI documentation.

David Lester Hall of Fame Points: 3647 More actions · Answer 10

Would these values be created out of a mainframe cobol program?