December 5, 2011 at 12:18 pm
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.
December 5, 2011 at 12:39 pm
: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.
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
December 5, 2011 at 12:45 pm
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/
December 5, 2011 at 12:58 pm
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.
December 5, 2011 at 1:00 pm
Thank you Sean I will try this and see.I appreciate your help.
December 5, 2011 at 1:01 pm
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/
December 8, 2011 at 6:54 pm
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
December 9, 2011 at 6:15 am
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.
December 9, 2011 at 1:19 pm
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
December 12, 2011 at 9:56 am
You'll have to dig a bit then. Sign up on BOB and start reading the BODI documentation.
December 13, 2011 at 11:39 am
Would these values be created out of a mainframe cobol program?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply