Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
detlion1643
Yak Posting Veteran
67 Posts |
 Posted - 2010-06-25 : 10:07:18
|
this select statement works wonders, viewing the data as I would like it to appear. However, I use a bunch of case checks because I don't like sql's built in isnumeric() function. The problem I ran across is putting the data into a table that can hold both strings and ints in the same field.[Part_#] can contain any combination of characters,numbers,etc... the datatype of the field is varchar(255), figuring it could hold either the strings or ints. Whenever full integers come across, the data resorts to some number with e in the middle. ex) 2.0044e+007.select cast(id as int),cast(sku as int),case when charindex('-',[part_#])>0 then [part_#] when charindex('=',[part_#])>0 then [part_#] when charindex('.',[part_#])>0 then [part_#] when charindex('/',[part_#])>0 then [part_#] when charindex('e',lower([part_#]))>0 then [part_#] when isnumeric([part_#])>0 then cast([part_#] as int) else [part_#] END,[sub category],[brand] from #temp1 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-25 : 10:12:03
|
| Can you post some sample data with expected result?MadhivananFailing to plan is Planning to fail |
 |
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-06-25 : 10:21:39
|
lets say I have a list of [part_#]'s like this: 20957091, 20957267, hfffk, dfffda, 20973872.It's a column in an excel sheet, and I am using openrowset like this:Select * into #tempage FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\pathhere\test.xls;HDR=YES;IMEX=1','SELECT [id],[sku],[part_#],[sub category],[brand] FROM [Parts$]')select cast(id as int),cast(sku as int),case when charindex('-',[part_#])>0 then [part_#] when charindex('=',[part_#])>0 then [part_#] when charindex('.',[part_#])>0 then [part_#] when charindex('/',[part_#])>0 then [part_#] when charindex('e',lower([part_#]))>0 then [part_#] when isnumeric([part_#])>0 then cast([part_#] as int) else [part_#] END,[sub category],[brand] from #temp1--insert into parts select cast(id as int),cast(sku as int),case when charindex('-',[part_#])>0 then [part_#] when charindex('=',[part_#])>0 then [part_#] when charindex('.',[part_#])>0 then [part_#] when charindex('/',[part_#])>0 then [part_#] when patindex('%[A-z]%',[part_#])>0 then [part_#] else cast(cast([part_#] as int) as varchar(255)) END,[sub category],[brand] from #temp1 drop table #temp1drop table #temp1The select * statement above the insert was to see what the data looks like before it inserts, which is how I want it to look. The part numbers exactly as they are listed. However, after inserting this into the table, all the number values will look like this: 2.0044e+007 while all the string values stay the same.EDIT - I am looking for all the values to stay the same, the numbers and strings |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-25 : 11:10:07
|
| Try thisSelect * into #tempage FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\pathhere\test.xls;HDR=YES;IMEX=1','SELECT [id],[sku],[part_#],[sub category],[brand] FROM [Parts$]')select cast(id as int),cast(sku as int),case when isnumeric([part_#])=1 then cast(cast([part_#] as int) as varchar(10)) else [part_#] end ,[sub category],[brand] from #temp1--insert into parts select cast(id as int),cast(sku as int),case when charindex('-',[part_#])>0 then [part_#] when charindex('=',[part_#])>0 then [part_#] when charindex('.',[part_#])>0 then [part_#] when charindex('/',[part_#])>0 then [part_#] when patindex('%[A-z]%',[part_#])>0 then [part_#] else cast(cast([part_#] as int) as varchar(255)) END,[sub category],[brand] from #temp1 drop table #temp1drop table #temp1MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
datatype confusion