SQL 2012 :: Select Rows With Blank Spaces

Feb 12, 2014

I got two columns DEATHDATE and STATUS

I have to update STATUS Column as INVALID when data in DEATHDATE column as follows

Example of INAVLID DEATH DATE : 1221, 12xt88,12#10, 1, ABC, #@ , etc

Do nothing when the DEATHDATE is VALID like

Examples of Valid/accepatable DEATHDATE: 09122012, 091212 ,******,********, 000000,00000000

How can i achieve this.

View 1 Replies


ADVERTISEMENT

Replace Blank Spaces

Oct 3, 2006

i have a field with blank spaces.
i wanna replace the spaces with just one spaces. ihave 500 fields in 500 tables.

any input will be appreacited.

i have something like this but its not working.

declare @field varchar(50)
declare @minVoter int
declare @maxVoter int
declare @tableName varchar(20)

set @tablename = '00001'


-- select ad_str1 from [00170]

select @minVoter = min(id_voter),
@maxVoter = max(id_voter) from quotename(@tablename)


while (@minVoter <= @maxVoter)
begin
select @field = ad_str1
from quotename(@tablename)
where id_voter = @MinVoter


update [00170]
set ad_str1 = replace(@field, ' ', ' ')

select @minVoter = min(id_voter)
from quotename(@tablename)
where id_voter > @minvoter

end

View 13 Replies View Related

How To Remove Blank Spaces In Records??

Jun 23, 2005

We imported approximately 2.9 million records from our mainframe server
into our SQL Server but have run into a problem.  The data in a
few of the fields contains both leading and trailing spaces.  An
example of the data would be like this, using periods to represent
spaces:

What we have:

..1A02938.....

What we need:

1A02938  (no spaces)

 Is there some sort of algorithm I can run on the data to remove
those spaces?  The problem is coming up when trying to perform a
SELECT query.  We try something like:

SELECT * FROM PCPIPT0 WHERE PANO20 = "1A02938"  but we get zero
results because of the spaces in the database.  The datatype of
the filed is char(20) because we need some flexibility on the size of
the data stored.

Any assistance would be greatly appreciated.

View 5 Replies View Related

Varchar And Blank Spaces In SQL DB Field

Jun 30, 2005

I am trying to load a field in my DB and it is defined as varchar(11) but when I populate it, it still adds spaces at the end. When I try to use it in an If statement, it doesn't match and executes the else instead. The wierd part is it seems to make it 10 characters long and not 11 or the the actual length. I think I had originally set it up for char(10) then changed it afterward but I even deleted the field and reentered it as varchar(11).Thanks,Eric

View 3 Replies View Related

How To Set 0 Instead Of Blank Spaces To A Column Or Field.

Aug 20, 2007

Hi i hv a doubt in Sql server reporting..I do generate some reports based on some criteria.In the results screen i hv empty fields based on the search i hv generated.I need to set "0" instead of blank spaces in the fields..Can any one help me?

View 5 Replies View Related

Replace Nulls With Blank Spaces In Float Data Type

May 8, 2008

Hello,
I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column.
The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file.
Thanks

View 7 Replies View Related

How To Remove The Blank Spaces Between Subreports In Sql Server Reporting Service?

Feb 14, 2007

In Sql server reporting service the blank spaces or white spaces are coming in between the subreports, when we place the subreports in the main reports.

If any one know how to remove the blank spaces between the subreports, please reply me. Its very urgent.

View 2 Replies View Related

Reporting Services :: How To Remove Blank Spaces After Removing Duplicated Records

Aug 18, 2015

I wanted to remove duplicate records from SSRS report. I set the "Hide Duplicates" to True. It is now working, But i am getting the space between the two records, which i want to get rid of. How to get rid of extra spaces between two records ( Please find the details below).

View 5 Replies View Related

SQL 2012 :: How To Select Top 200 To 300 Rows From A Table

Mar 9, 2015

Select top 100 * from tab1 order by col1 ---gives me the top 100 order by col1

Select top 200 * from tab1 order by col1 ---gives me the top 200 order by col1

How do I select the top 100 to 200

View 3 Replies View Related

SQL Server 2012 :: Select Matching Rows Which Exist In Particular String?

Apr 14, 2015

Suppose I have string like

DECLARE @strname varchar(50) = 'i_ncm_ai04';
DECLARE @SAMPLE_DATA TABLE
(
SD_ID INT NOT NULL
,SD_TEXT VARCHAR(10) NOT NULL

[Code] ......

how can I search a ID's which are exist in my string.

result should be,

3 i_ncm
8 i_ncm_a

View 2 Replies View Related

SQL Server 2012 :: Select Rows With Sum Of Column From Joined Table?

May 2, 2015

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.

What I tried, but what returns NULL for purchasedamount:

SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2

How can I achieve what I need?

Here are my table definitions and data:

/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,

[code].....

View 0 Replies View Related

Blank Spaces In Table Name, Cannot Delete Table

Mar 10, 2000

A table was created in version 6.5 that has 2 blank spaces and a slash '/' in it's name (i.e. Item Type w/Groups). This was done in error and now the table cannot be dropped or renamed and dropped.

Does anyone know how I can delete this table? Please let me know.

View 1 Replies View Related

Extra Spaces In Rows

Jul 10, 2006

I have some fields in my database that are char(50)

I use an insert to put some text into the fields (using perl)
when I look at the fields I find that spaces are used to "pad out" the text to the field size.

This didn't happen with MySQL and PHP, is there any way to disable this?

View 5 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

Transact SQL :: How To Compare Rows With Spaces

Nov 17, 2015

I would like to compare rows from 2 tables.

For ex:

Table1
Col1 Col2 Col3
1      a     aa
2      b     bb

Table2
Col1 Col2 Col3
1        a   aa
2      b     bb

Notice Table2.Col2 first row has an extra space " a", where Table1.Col2 value is "a".I need to compare all the columns, not just 1 column (any of the column can be different). Do we use CHECKSUM for this, or is there a better way to compare the 2 tables (in this case resulted in Table2 row 1 col 2 being the difference ?

View 10 Replies View Related

Supress Blank Rows

Jan 31, 2007

How do I get a Report Table to supress blank rows from displaying ?

In Crystal Reports this option is available under Format Detail Section, supress blank section chekckbox.

View 9 Replies View Related

Add Spaces To Select Paramter

Oct 30, 2007



Hi all,

I have a stored procedure that one of the select parameters is a NCHAR (250)

I need to select and RTRIM() is, and add 3 spaces

I've tried many options, but each time I get the full trimmed version of the column

This is what I've tried:


SELECT (RTRIM(c.customer_name) + space(3)) as CustomerName

SELECT (RTRIM(c.customer_name) + ' ') as CustomerName
SELECT (RTRIM(c.customer_name) + replicate(' ',3)) as CustomerName



Any suggestions?

Cheers

View 8 Replies View Related

Entering Dates In Blank Rows

May 9, 2007

I'm hoping that someone can help. This is my first time posting and fortunately I can normally find what I need but this time I am stumped.

I have a query that produces a date range. The problem is I want to insert or at lease display the dates between even if they don't show up in table. Here's the problem.

My intial query is
select date_of_call
from call_data
where date_of_call >= '2001-09-01' and date_of_call <= '2001-09-15'
order by date_of_call

Results:
2001-09-03 00:00:00.000
2001-09-07 00:00:00.000
2001-09-10 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-14 00:00:00.000

When I do a group by and count it looks like this:
select date_of_call, count(date_of_call) as Count_Date_of_Call
from call_data
where date_of_call >= '2001-09-01' and date_of_call <= '2001-09-15'
group by date_of_call
order by date_of_call

Date_Of_Call, Count_Date_of_Call
2001-09-03 00:00:00.0001
2001-09-07 00:00:00.0001
2001-09-10 00:00:00.0001
2001-09-13 00:00:00.0005
2001-09-14 00:00:00.0001

If you notice out of 15 days it only shows 5 days. I am wondering how I can insert the days missing and insert either null or 0 values in the count column so it would look something like this:

Date_Of_Call, Count_Date_of_Call
2001-09-01 00:00:00.0000
2001-09-02 00:00:00.0000
2001-09-03 00:00:00.0001
2001-09-04 00:00:00.0000
2001-09-05 00:00:00.0000
2001-09-06 00:00:00.0000
2001-09-07 00:00:00.0001
2001-09-08 00:00:00.0000
2001-09-09 00:00:00.0000
2001-09-10 00:00:00.0001
2001-09-11 00:00:00.0000
2001-09-12 00:00:00.0000
2001-09-13 00:00:00.0005
2001-09-14 00:00:00.0001
2001-09-15 00:00:00.0000

Any help would be much appreciates.

View 5 Replies View Related

Blank Rows In Export To Excel

Mar 13, 2007

Hi All,

Few days back we faced a problem when we were doing an Export to Excel of a report which was using sub-reports. After going thro. the knowledge base articles we came to know that SSRS currently doesn't suppot this option, an alternate is to use use List instead of tables, so we used List and did all the formatting with the list and the data was displayed correctly and it was Exporting to Excel also properly. But now we found out that after doing an Export to Excel, the Excel File is leaving blank rows between the data (i.e.,) If the report consists of two rows, then it displays the first row in the 10th row of excel and the second row in the 12th row of excel. The 11th row is blank and it appears as a small blank row between 10 and 12. The actual problem because of this is we are not able to do Auto filter in Excel, because Excel by default considers the values only until it encounters a blank row when we are doing auto filter. So is there a way to avoid this blank row while exporting to Excel, we have tried to remove the borders and all other stuffs but nothing seems to work. Have anyone encountered the same problem or is there any work around for this problem. Thanks in advance.

View 5 Replies View Related

SQL Server 2012 :: How To Trim Spaces For File Name

Apr 23, 2015

using below script to read two column from a text file and trying to trim the spaces for file_name.but trimming is not happening

bulk insert #O_P
from 'C:UsersTKTPA.txt'
with (fieldterminator = ',',rowterminator = '')
END
INSERT INTO O_P (file_name,location)
SELECT LTRIM(RTRIM(file_name)), location From #O_P

View 4 Replies View Related

Transact SQL :: Remove Blank Rows From Table?

Apr 17, 2012

I have a table for example like following

DECLARE @tmpTable table
(
name varchar(10),
address1 varchar(10),
phnno varchar(10),
mobno varchar(10)
)
INSERT INTO @tmpTable(name,address1,phnno,mobno)

[Code] ....

I want to remove all empty rows like row 1,2 and 3 in the above example.

I can't check all columns null values as there are many columns in my actual table.

View 6 Replies View Related

SQL 2012 :: How Server Fills Empty Spaces In Data Files

Oct 1, 2015

I understand that we shouldn't shrink data files as it might cause heavy fragmentation along with log usage, high IO/CPU etc.

In a DB in which lot of DML transaction occur, there will be empty spaces whenever deletions occur.

Will SQL Server fill that part with data when new insertions occur ?.

View 4 Replies View Related

SQL 2012 :: SSIS - Export Flat File As CSV With Padded Spaces

Oct 5, 2015

From SSIS I need to export data to a CSV with spaces padding the end of each field before the delimited value. For example if I have three fields that are Nvarchar(10) I need it to be this:

Testing ,Test123 ,Again {end of line}

instead of this:

Testing,Test123,Again{end of line}

It's like it can do fixed width or delimited but not both. Is this possible without having to force the spaces into the data coming back from SQL? I already have the SSIS package written to export the data to CSV which works great, just need to find some way to add the spaces to the end of each column to satisfy requirements on the system being exported to. Also the commas need to be there too.

View 3 Replies View Related

SQL 2012 :: Blank (Empty String) In Primary Key

Apr 29, 2014

Can we have blank value in Primary key field? Two fields are chosen as Primary Key for this table but the issue is at any given time one value will be blank when other is populated. Does sql server still order the records based on these keys? (Clustered index). Or should I just go about adding ID (identity)?

View 1 Replies View Related

SELECT Statement For Fields That Are Blank?

Jan 9, 2008

I thought this was working, but apparently it was not. I was wondering how I would create a SELECT Statement for values that are blank (Equal to ""). I really could have swore that this was working, but I guess it wasn't.

Here is my code:





Code Block

try
{
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"MyDB.accdb;Jet OLEDB:Database Password=MyPassword;";

// create an open the connection
OleDbConnection conn = new OleDbConnection(conString);
OleDbCommand command = new OleDbCommand();
command = conn.CreateCommand();

// create the DataSet
DataSet ds = new DataSet();

// run the query
command.CommandText = "SELECT ID AS [#], Company AS [Company], FName AS [First Name], LName AS [Last Name], Type AS [Type] FROM Table1 WHERE Tags = @P0;";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter = new OleDbDataAdapter(command);
command.Parameters.Add("@P0", OleDbType.VarChar).Value = "";
adapter.Fill(ds);

// close the connection
conn.Close();

bindingSource1.DataSource = ds.Tables[0];

DataGridView.DataSource = bindingSource1;

// set the size of the dataGridView Columns
this.DataGridView.Columns[0].Visible = false;
this.DataGridView.Columns[1].Width = 234;
this.DataGridView.Columns[2].Width = 50;
this.DataGridView.Columns[3].Width = 50;
this.DataGridView.Columns[4].Width = 75;

//Sort on the Title Column
DataGridViewColumn sortColumn = DataGridView.Columns[1];
ListSortDirection direction;
direction = ListSortDirection.Ascending;
DataGridView.Sort(sortColumn, direction);

//Set the Selected Property of the First Row to False
DataGridView.Rows[0].Selected = false;
}
catch
{
}

Thank you again for your help.

View 8 Replies View Related

REPLACE In Derived Column Transform Causing Repeat Data In Rows That Should Be Blank

Jul 25, 2006

W2k3 server, SQL 2005.
@@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Standard Edition on Windows NT 5.2
(Build 3790: Service Pack 1)

I have my first SSIS package almost working, but I'm having an odd problem and can't find any information to help resolve it.

I'm importing from a flat file (csv) to an existing table (append). I've got a Derived Column transformation in the middle to do some data cleanup. It's all working except for one little problem...

One of the transformations is 'REPLACE([Column 3],"^","; ")', output to a new column. (The input file has a field that uses carets as delimiters between an unknown number of items; I'm changing that to semicolons for easier reading.) Not all rows have data in this column, some will have one item, some will have multiple items.

The REPLACE works except that it fills in repeated data for all the blank rows.

Example:

Incoming data is:

1 Smith,Jane^Jones,Jane

2 Brown,John

3

4 Adams,James^Adams,Jim

5

6 White,Debra

Data inserted into the table is:

1 Smith,Jane; Jones,Jane

2 Brown,John

3 Brown,John

4 Adams,James; Adams,Jim

5 Adams,James; Adams,Jim

6 White,Debra

I've tried to use a Conditional to skip the empty rows, but I can't get that working at all (get syntax errors no matter what I put in).

Any suggestions on how to fix this would be most appreciated!

Thank you.

View 5 Replies View Related

SQL Server 2008 :: SSIS Derived Column Transformation Failing On Converting Blank Rows

Jul 30, 2015

I have flat file source from which data is imported to a Sql table.The target column is int and input column is string .The column has some numeric values and some blank values.when I tried to convert into int values it fails.

View 7 Replies View Related

SQL Server 2012 :: Query Tuning With Each Run Of Fresh / Blank CACHE

Jun 4, 2015

I am using SQL Server 2012 Express.

I am doing performance tuning of SP/Query in Dev-Test environment.

I found that SQL Server caches plan between successive executions.

So if I test/execute SP 10 times, after 1st or 2nd execution, SQL server will pull-up plan-info from CACHE...Not from SQL SERVER Or Database...

Means i am not getting correct answer...

I found this 2 commands:

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

But they say that executing above command might interfere/bother other people executing other query/sp on this server.

They also say that: Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.

Part of query was using Dynamic-SQL executed with EXEC command.

I replaced that with SP_EXECUTESQL.

How can I start testing of each SP-run with Fresh/Blank CACHE ?

View 1 Replies View Related

Select Query From A Stored Proc When The Values Can Be Blank.

Oct 10, 2006

Hi All,

I think what am trying to do is quite basic.

I have 3 paramaters@value1, @value2,@value3 being passed into a stored
proc and each of these parameters can be blank. If one of them is blank
and the rest of them have some valid values, then I should just exclude
the column check for the value that is blank.

For e.g if all my parameters being passed are non- empty then I would
do this
select * from tblName
where column1 like @value1 and column2 like @value2 and column3 like
@value3

else if I have one of the parameter being passed as empty, I should
ignore that parameter like
if@value1 is empty then my sql should be

select * from tblName
where column2 like @value2 and column3 like @value3

I don't want to do a dyanmic sql because of rights and security issue.
I want it through a stored procedure only.

Also, all the three columns can have null values in the table.
Please let me know what is the best possible way to do this. Thanks in
advance !.

.noscripthide
{display:none;}
.noscriptinline
{display:inline;}
.noscriptblock
{display:block;}

.scripthide
{display:none;}
.scriptinline
{display:inline;}
.scriptblock
{display:block;}

.script12hide
{display:none;}
.script12inline
{display:inline;}
.script12block
{display:block;}
.lnav
{position:absolute;}
.lnavch
{margin-left:23.0ex;}

.script13hide
{display:none;}
.script13inline
{display:inline;}
.script13block
{display:block;}

.hide
{display:none;}
.hide_ie
{;}
.hide_ie
{display:none;}
img
{border:0;}
img
{border-color:#0000a0;}
input.ck
{margin-left:-2px;}
input.bt, button.bt
{padding:0 .4em 0 .4em;width:auto;overflow:visible;}
input.bt, button.bt
{width:1px;}
.fixed_width
{font-family:fixed-width, monospace;font-size:90%;}
a:visited
{color:#551a8b;}
a:active
{color:#f00;}
.inheritcolor a
{color:inherit;}
.minmaxwie
{width:100%;}
* html .minmaxwie
{;}
.fl:visited
{color:#551a8b;}
.fl:active
{color:#f00;}
.fl:link
{color:#7777CC;}
.z
{display:none;}
.on:active
{color:#f00000;}
.don:active
{color:#f00000;}
.mbody
{margin-top:4px;}
body,td,input,textarea,select
{font-family:arial,sans-serif;}
body,td
{font-size:83%;}
input,textarea,select
{font-size:100%;}
form
{margin:0;}
.tick
{font-family:webdings;text-decoration:none !important;}
.qr
{width:100%;padding:4px;font-family:arial,sans-serif;}
.nu
{text-decoration:none;}
.gt
{border-collapse:collapse;}
.gt td
{padding:.3em 4px;border-right:1px solid #ffcc33;}
.gm td
{padding:.3em 1em .3em 0px;}
.bnk
{border:1px solid #ffcc33;}
.bnk td
{border-right-width:0px !important;}
.sel td.seltd
{padding:4px 4px 4px 4px;border:1px solid #ffcc33;border-right:none;font-weight:bold;}
p.b
{margin-bottom:1.5em;margin-top:.3em;}
.adb, .adbrnav
{border-left:1px solid #fff4c2;}
.msgdate
{color:#676767;}
.md
{color:#555555;}
.st
{margin-left:-1px;}
.nb
{white-space:nowrap;}
.np
{padding:0px;}
.p
{font-weight:bold;}
.mo
{margin:.5em 0 0 0;}
.oa
{padding:2px .5em;}
.sbox
{margin-top:1em;margin-bottom:1em;}
button a:link
{text-decoration:none;color:black;}
button a:hover
{text-decoration:none;color:black;}
.b
{font-weight:bold;}
.fontsize0
{font-size:78%;}
.fontsize1
{font-size:87%;}
.fontsize2
{font-size:96%;}
.fontsize_25
{font-size:100%;}
.fontsize3
{font-size:108%;}
.fontsize4
{font-size:120%;}
.fontsize5
{font-size:133%;}
.fontsize6
{font-size:150%;}
.fontsize7
{font-size:150%;}
.cv
{width:100%;}
.lk
{color:#0000CC;text-decoration:underline;cursor:pointer;}
.nl
{padding:5px 0 2px 5px;}
.tsh
{border-top:1px solid #ffcc33;}
.tlsh
{border-top:1px solid #ffcc33;}
.blsh
{border-bottom:1px solid #ffcc33;}
.bsh
{border-bottom:1px solid #ffcc33;}
.lsh, .tlsh, .blsh
{border-left:1px solid #ffcc33;}
.lnav
{left:9px;width:23.0ex;overflow:hidden;}
.lnavch
{;}
.lnavi
{font-size:100%;}
.lnavim
{margin-left:-2px;}
* html .lnav
{left:11px;}
.alertboxout
{margin:5px 15px 0px 10px;clear:left;}
.alertboxin
{clear:left;color:black;background-color:#fad163;font-weight:bold;text-align:center;padding:0px 15px 0px 15px;position:relative;margin:-1px 0px;}
.ctl
{padding-left:2px;}
.mb
{padding:6px 8px 0 5px;}
.exh
{margin:0 0 0 5px;background-color:#e8e8e8;}
.exh div div
{padding-top:4px;}
.thread_star
{padding:0 0 4px 2px;}
* html .thread_star
{padding:0 0 0 2px;}
.blurb_star
{padding:0 0 0 2px;}
* html .blurb_star
{padding:2px 0 0 2px;}

View 7 Replies View Related

SQL 2012 :: Blank Database Created On Two Separate Disks - Write To Multiple Files

Mar 17, 2014

I am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).

I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.

Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.

I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:

;WITH CTE AS
(SELECT
sys.fn_PhysLocFormatter (%%physloc%%) col1,
RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID],
DATAID

[Code] ....

I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?

By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?

View 6 Replies View Related

Help Needed Convert A Blank In Char To Blank In Float

Apr 29, 2008

Hi,

I receive blanks for a column called value and i need to represent it as a blank or NA into a colum whose datatype is float in the datawarehouse.

how is this possible because in current schenario a blank is being converted to 0
which is not the right thing.

i would like to retain the blank in conversion from char to float ,

please explain me if it can be done or how to overcome this issue.

Thanks

View 1 Replies View Related

Select Distinct Rows From Duplicate Rows....

Nov 28, 2007

Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!

View 4 Replies View Related

SQL Server 2012 :: Update Statement Will Not Update Data Beyond 7 Million Plus Rows Out Of 38 Millions Rows

Dec 12, 2014

I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).

SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved