How to Replace Empty String With Value That Is Not Empty for the Same Policynumber

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 strings :

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 NaNs and then use bfill for back forward filling NaNs 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:

Sample Image

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



Leave a reply



Submit