Select everything to the right of a specific character

Question

Given this data:

Home: (708) 296-2112

I want everything to the right of the : character.

This is what I have so far, but I'm getting no results:

right(phone1, locate(':', phone1 + ':')-1) phone

If I use left instead of right, I get just "HOME" - just for testing purposes. I know I'm close, but I'm missing something.

How about `substring(phone1, locate(':', phone1) + 1, length(phone1))` — Alex K., May 17 '19 at 13:46
@jarlh The answer is yes, see one of the (now) deleted answers. — Tim Biegeleisen, May 17 '19 at 13:52
You should add some more sample data, with different number types, and also the expected result. — jarlh, May 17 '19 at 13:54

Nick · Accepted Answer · 2019-05-17T13:52:16.743

1

You can use SUBSTRING (might be SUBSTR dependent on your version) instead:

SELECT SUBSTRING(phone1, LOCATE(':', phone1) + 1, LENGTH(phone1))
FROM yourtable

edited May 17 '19 at 13:52

answered May 17 '19 at 13:47

Nick

138,499
22
57
95

i dont think stubstring or substr works with advantage. or at least ive never got it working – Brian K May 17 '19 at 13:50
sorry i was doing something wrong, thanks that worked. – Brian K May 17 '19 at 13:54

score 1 · Answer 2 · answered May 17 '19 at 13:47

Here's a way to do it without hard-coding in Home:, so you can also use Office: or Mobile: or Fax:, or any other word followed by a colon.

This uses ADS's scripting ability to use a variable and the built-in System.iota single row table (similar to Oracle's dual). You can just use the last line, replacing test with the name of your column and system.iota with the name of your table.

declare test string;
set test = 'Home: (708) 296-2112';
select substring(test, position(':' in test) + 1, length(test)) from system.iota;

score 1 · Answer 3 · answered May 17 '19 at 13:54

1

You can use RIGHT function as follows:

RIGHT(phone1, LEN(phone1)-CHARINDEX(':', phone1))

answered May 17 '19 at 13:54

Apoorv Mishra

1,489
1
8
12

score 0 · Answer 4 · answered May 17 '19 at 13:47

0

You were on the right track, but your algebra is off. You want to take the full length of the string offset by the position of the colon, minus one:

right(phone1, length(phone1) - locate(':', phone1) - 1)

answered May 17 '19 at 13:47

Tim Biegeleisen

502,043
27
286
360

I ran through the logic in my answer several times, and it checked out each time. I don't know what to suggest to you `\:-|` – Tim Biegeleisen May 17 '19 at 13:52

Select everything to the right of a specific character

4 Answers4