SUBSTRING Empty Value Alternatives

javascript substring
javascript slice vs substring performance
es6 substring
jsx substring
angular substring
date substring
substring from end
json substring

I have the following query which works and does what is supposed to do:

select 
SUBSTRING(LastName,2,1)
+SUBSTRING(LastName,3,1)
+SUBSTRING(LastName,5,1) 
+SUBSTRING(FirstName,2,1) 
+SUBSTRING(FirstName,3,1)
+replace(convert(varchar, DateOfBirth,101),'/','')
+CASE WHEN GenderID = '1' Then '1' WHEN GenderID = '2' Then '2' ELSE '9' END
from 
    Client

However if any of the SUBSTRINGS return an empty string, it needs to be replaced with 2. For example if LastName is Bond, SUBSTRING(LastName,5,1) needs to return a 2.

How would anyone suggest this is done? I was trying to avoid something like this:

select CASE WHEN SUBSTRING('James',5,1) = '' THEN '2' ELSE SUBSTRING('James',5,1) END

I think this is the simplest method:

select (SUBSTRING(LastName + '22222', 2, 1) +
        SUBSTRING(LastName + '22222', 3, 1) +
        SUBSTRING(LastName + '22222', 5, 1)  +
        SUBSTRING(FirstName + '22222', 2, 1) +
        SUBSTRING(FirstName + '22222', 3, 1) +
        replace(convert(varchar(255), DateOfBirth, 101), '/', '') +
        (CASE WHEN GenderID IN ('1', '2') THEN GenderID ELSE '9' END)
       )
from Client;

This just adds enough '2's to the end to be sure that the substring() finds a character.

Note two other changes. I added a length parameter to varchar(). You should always use a length parameter, because the default length varies by context -- leading to hard-to-find errors. I also simplified the logic for GenderId (it assumes that GenderId is a string).

JavaScript: Slice, Substring, or Substr?, In JavaScript, substrings are primarily extracted through one of following value as the start index and largest value as the stop index. substring will end of the string and I feel that returning an empty string when start index  Although your substring may not contain all blanks, you may have a blank or blanks between the numeric values or the substring may contain non-numeric values. If you try to parse a non-numeric value it will cause an exception and cause your application to crash, the try and catch block help you deal with the exception so that your application can continue to run.

Here is one trick:

SELECT
    LEFT(SUBSTRING(LastName, 2, 1) + '2', 1) AS first_part
FROM Client

Should the call to SUBSTRING return an empty string output, then the call to LEFT would return '2'. Otherwise, LEFT('#2', 1) would return the number from the call to SUBSTRING (here I use # to represent any number).

As @dnoeth pointed out in his comment, this solution would only work if the substring length is 1, which appears to be the case for your query/data.

JavaScript : String.slice() vs String.substring() vs , Use negative values to specify the position from the end of the string. console.​log(numbers.slice( 100 , 101 )); // shows (an empty string)  You can easily right click on any desired value in Power Query, either in Excel or Power BI, or other components of Power Platform in general, and simply replace that value with any desired alternative. Replacing values based on certain conditions however, may not seem that easy at first.

TRY THIS: If you wish to use IIF for SQL SERVER 2012 or +

SELECT IIF (SUBSTRING('James',6,1)='', '2', SUBSTRING('James',5,1))

Perl Pocket Reference, Default value is a newline. Alternatives: $OUTPUT−FIELD−SEPARATOR, $OFS. The output field separator for the print functions. Default value is an empty string  Not necessarily. An empty string is either not initialised or can only consist of a null terminator. An empty string is not a substring since it has no value. The size will be 0.Meaning there is no character assigned to it.

Oracle REPLACE, In case the string_pattern is null or empty, the REPLACE() function returns the INSERT INTO articles( title, article_body) VALUES('Sample article','This is a while the REPLACE() function allows you to substitute one string for another. If we don’t specify last, this value is used. Since our example vector is shorter than 1000000L, the whole rest of the vector after position 7 is printed. Example 3: Replace Substring with substr() & substring() Another popular usage of the substr and substring R functions is the replacement of certain characters in a string.

substring, A negative value or zero (0) returns an empty string. Return type. VARCHAR. Examples. SUBSTR('breakfast', 1) returns 'breakfast  To extract a substring that begins with a particular character or character sequence, call a method such as IndexOf or LastIndexOf to get the value of startIndex. If the substring extends from startIndex to a specified character sequence, you can call a method such as IndexOf or LastIndexOf to get the index of the ending character or character sequence. You can then convert that value to an index position in the string as follows:

MySQL 5.6 Reference Manual :: 12.7 String Functions and , Returns the numeric value of the leftmost character of the string str . Returns 0 if str is the empty string. Returns NULL if str is NULL . ASCII() works for 8-bit  A value of 1 indicates that the first character of the substring is the first character of string-expression. The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34) which is then assigned to an INTEGER value.

Comments
  • You don't want to use CASE, because it doesn't look nice, but it's mainly cut&paste&modify, easier to understand than LEFT or ISNULL/NULLIF and not less performant than those :-)
  • Sample data and desired results would really help.
  • This only works for a length of 1, otherwise you might get a wrong result, e.g. select LEFT(SUBSTRING('foo', 3, 2) + '2', 2)
  • @dnoeth I assume that the substring call will always cover actual in-bounds text. Yes, as you pointed out, my answer would fail if this be not the case. I will add a disclaimer to my answer.
  • If the substring always covers actual in-bound text there would be no need for replacing an empty string :-) Of course your solution is nice for the single character returned by the orignal select.
  • @dnoeth I've been chewing on your comment for the last 5 minutes, and I see what you mean now. I'll leave this up with the caveats you have mentioned.