How to replace empty string with value that is not empty for the same PolicyNumber
Correlate the subquery or you can use LEAD
.
select PolicyNumber,
QuoteID,
CoverageType,
ClaimType,
case when ClaimType = ' ' then (
select top 1 ClaimType
from @ClaimType c
where
x.PolicyNumber = c.PolicyNumber
and x.CoverageType = c.CoverageType
order by QuoteID desc
)
else ClaimType End as ClaimType1
from @ClaimType x
select
PolicyNumber
,QuoteID
,CoverageType
,ClaimType
,ClaimType1 = case when ClaimType = '' or ClaimType is null then lead(ClaimType) over (partition by PolicyNumber, CoverageType order by QuoteID) else ClaimType end
from @ClaimType
If there are multiple blanks or the pattern breaks where the QuoteID = 2
isn't the one that is populated, I'd as a where clause to the sub query. Notice the extra row I added and the where clause addition.
declare @ClaimType table (PolicyNumber varchar(50), QuoteID int, CoverageType varchar(50), ClaimType varchar(100))
insert into @ClaimType values ('00001',1, 'CGL', ' '),
('00001',2, 'CGL', 'Occurrence'),
('00002',1, 'CPL', ' '),
('00002',2, 'CPL', 'Occurrence-Pollution'),
('00002',3, 'CPL', '') --added this row
select PolicyNumber,
QuoteID,
CoverageType,
ClaimType,
case when ClaimType = ' ' then (
select top 1 ClaimType
from @ClaimType c
where
x.PolicyNumber = c.PolicyNumber
and x.CoverageType = c.CoverageType
and c.ClaimType != '' --added this clause
order by QuoteID desc
)
else ClaimType End as ClaimType1
from @ClaimType x
REPLACE empty string
There is nothing to replace in an empty string. REPLACE
replaces a sequence of characters in a string with another set of characters.
You could use NULLIF
to treat it as NULL
+ COALESCE
(or ISNULL
):
declare @value varchar(10);
set @value = '';
SELECT COALESCE(NULLIF(@value,''), '0')
This returns '0'
.
ISNULL with Empty String for alternate still returns some NULLs
I think you'll find that the subselect actually returns NULL in those cases where no rows are found.
In cases where a row is found in TagNames_CTE
where the where
clause fires and TagNames
is NULL, it will get converted to ''
by the function.
But where no rows are found at all, the function is not called (since there are no rows for it to work its magic on) and the result of the overall subselect is NULL, because it has to return something in that column for the outer select.
An easy way to check is to run the subquery (without ISNULL()
) on its own and see if it returns a row containing NULL
or no rows at all - try some simple queries along the lines of:
select 1,
(select isnull (null,2) from dummytable where 1 = 0)
from dummytable
and:
select 1,
(select isnull (null,2) from dummytable where 1 = 1)
from dummytable
You'll probably find that the former gives you 1, null
while the latter gives you 1,2
. Having tested that in MySQL with the following statement, you can see that this is most likely correct.
> create table xyzzy (plugh integer);
> insert into xyzzy values (42):
> select 1,(select ifnull (null, 2) from xyzzy where 1 = 0) from xyzzy;
1 NULL
> select 1,(select ifnull (null, 2) from xyzzy where 1 = 1) from xyzzy;
1 2
fill up empty values with same value of another column in pandas dataframe
I think you need groupby
+ transform
:
If only one same category per group and no data are empty string
s :
df['POLICY NUMBER'] = (df.groupby('PRODUCT TYPE')['POLICY NUMBER']
.transform(lambda x: x[x != ''].iat[0]))
print (df)
POLICY NUMBER PRODUCT TYPE
0 433M86968 MED
1 433M86968 MED
2 433M86968 MED
3 433M86968 MED
4 566D158635 TED
5 566D158635 TED
6 566D158635 TED
7 566D158635 TED
Or if posible there are not always empty stings, but sometimes there are wtrailing whitespaces
, need strip
:
df['POLICY NUMBER'] = (df['POLICY NUMBER'].str.strip().groupby(df['PRODUCT TYPE'])
.transform(lambda x: x[x != ''].iat[0]))
print (df)
POLICY NUMBER PRODUCT TYPE
0 433M86968 MED
1 433M86968 MED
2 433M86968 MED
3 433M86968 MED
4 566D158635 TED
5 566D158635 TED
6 566D158635 TED
7 566D158635 TED
Solution with sorting and transform last
value:
df['POLICY NUMBER'] = (df.sort_values(['PRODUCT TYPE','POLICY NUMBER'])
.groupby('PRODUCT TYPE')['POLICY NUMBER']
.transform('last'))
print (df)
POLICY NUMBER PRODUCT TYPE
0 433M86968 MED
1 433M86968 MED
2 433M86968 MED
3 433M86968 MED
4 566D158635 TED
5 566D158635 TED
6 566D158635 TED
7 566D158635 TED
EDIT: You need replace empty strings by NaN
s and then use bfill
for back forward filling NaN
s with ffill
for forward fillin NaNs:
df['POLICY NUMBER'] = (df['POLICY NUMBER'].str.strip()
.replace('',np.nan)
.groupby(df['PRODUCT TYPE'])
.transform(lambda x: x.bfill().ffill()))
print (df)
POLICY NUMBER PRODUCT TYPE
0 433M49763 MED
1 433M49763 MED
2 433M49763 MED
3 433M86968 MED
4 566D158635 TED
5 566D158635 TED
6 566D158635 TED
7 789D158635 TED
Unexpected behavior from CONCAT in sql server with some strange, non null value : the value after it are not added to the result
That's bad data and is exactly what I would expect:
0x00
is the zero-byte character and effectively terminates the string.
You need to fix the source to not send 0x00, because the data is already gone.
How to check if the value is NULL then pick the one that is not null per PolicyNumber. SQL Server 2012
Using LAG
declare @TestTable table (ID int, PolicyNumber varchar(50), PolicyType varchar(50))
insert into @TestTable values
(001, 'ENV1','Primary'),
(002, 'ENV1',NULL),
(003, 'ENV2','Claim Made'),
(004, 'ENV3','Claim Made'),
(005, 'ENV3',NULL)
select ID,
PolicyNumber,
PolicyType = case when PolicyType is null then lag(PolicyType) over (partition by PolicyNumber order by ID) else PolicyType end
from @TestTable
Naturally, if there are consecutive NULL
values, we can handle this a couple of ways. Here is one:
declare @TestTable table (ID int, PolicyNumber varchar(50), PolicyType varchar(50))
insert into @TestTable values
(001, 'ENV1','Primary'),
(002, 'ENV1',NULL),
(003, 'ENV2','Claim Made'),
(004, 'ENV3','Claim Made'),
(005, 'ENV3',NULL),
(006, 'ENV3',NULL)
select t.ID,
t.PolicyNumber,
PolicyType = case when t.PolicyType is null then (select top 1 PolicyType
from @TestTable
where PolicyType is not null
and PolicyNumber = t.PolicyNumber
order by ID desc)
else PolicyType end
from @TestTable t
Why does searching for a string of n space characters return inconsistent results?
Per the ANSI SQL standard, trailing whitespace is often ignored for character field comparisons. So this:
SELECT 1 WHERE '' = ' '
Will return 1
.
KB316626 INF: How SQL Server Compares Strings with Trailing Spaces
describes this behavior (emphasis mine):
SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2,
<Comparison Predicate>, General rules #3) on how to compare strings
with spaces. The ANSI standard requires padding for the character
strings used in comparisons so that their lengths match before
comparing them. The padding directly affects the semantics of WHERE
and HAVING clause predicates and other Transact-SQL string
comparisons. For example, Transact-SQL considers the strings 'abc' and
'abc ' to be equivalent for most comparison operations.The only exception to this rule is the LIKE predicate. When the right
side of a LIKE predicate expression features a value with a trailing
space, SQL Server does not pad the two values to the same length
before the comparison occurs. Because the purpose of the LIKE
predicate, by definition, is to facilitate pattern searches rather
than simple string equality tests, this does not violate the section
of the ANSI SQL-92 specification mentioned earlier.[...]
The SET ANSI_PADDING setting does not affect whether SQL Server pads
strings before it compares them. SET ANSI_PADDING only affects whether
trailing blanks are trimmed from values being inserted into a table,
so it affects storage but not comparisons.
Note that they also cover the discrepancy you're finding with LIKE.
Brent Ozar blogs about this behavior here.
SQL Server : creating derived column
This can be done using a case statement:
SELECT *
, CASE WHEN NextPolicy <> '' AND ExpiryDate < CAST(GETDATE() AS DATE) THEN 'Yes'
WHEN NextPolicy = '' AND ExpiryDate < CAST(GETDATE() AS DATE) THEN 'No'
WHEN NextPolicy = '' AND ExpiryDate > CAST(GETDATE() AS DATE) THEN 'N/a yet'
END AS RenIndicator
FROM #mytable
I've used the empty string ''
for the "does not have any value" requirement, but if you have NULLS in those fields you will need to do something like ISNULL(NextPolicy, '')
in the CASE to account for them.
We are assuming that any value is a valid policy number as well. Additional data validation could be included as needed.
Also upcoming edit pending more information on what happens when the ExpiryDate is today's date.
Is there an alternative to string.Replace that is case-insensitive?
From MSDN
$0 - "Substitutes the last substring matched by group number number (decimal)."
In .NET Regular expressions group 0 is always the entire match. For a literal $ you need to
string value = Regex.Replace("%PolicyAmount%", "%PolicyAmount%", @"$$0", RegexOptions.IgnoreCase);
Related Topics
Select Distinct Values from One Table and Join With Another Table
How to Compare Datetime With Only Date in SQL Server
How to Close Idle Connections in Postgresql Automatically
Find All Parent Records Where All Child Records Have a Given Value (But Not Just Some Child Records)
Use Current Date as Default Value for a Column
Get Last Record of a Table in Postgres
Comma Separated Values in SQL Server Returning Duplicates
Removing Null Value in SQL Join and Union Operators in SQL
How to Find Multiple Occurrence of Particular String and Fetch Value in SQL Server
How to Remove Line Feed Characters When Selecting Data from SQL Server
Sql Server How to Return Null Instead of 0 If a Grouped Value Doesn't Have Rows in Source
Er_Access_Denied_Error: Access Denied for User ''@'Localhost' (Using Password: No)
How to Select the Last Record from MySQL Table Using SQL Syntax
Fill a Field With the Values from Another Field in Ms Access
Replace Default Null Values Returned from Left Outer Join