SUBSTRING Empty Value Alternatives
substring from end
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).
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
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.
TRY THIS: If you wish to use
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.
- 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.