CODEWITHSUNDEEP
sql
allencoded
allencoded

Reputation: 7275

SQL Substring a range of data

I want to select out just the email address using SubString.

Here is my column data:

[{"IsPrimary":false,"Address":"[email protected]","Type":"Other"}]

Here is my Query:

SELECT SUBSTRING(EmailJson, CHARINDEX('ess":"', EmailJson)+6, CHARINDEX('","Type', EmailJson)) From Respondents

Problem is that it isn't working the way I thought substring would work. I expected it to give me a range of characters. For example I want substring to return a range of characters like 5-10. The way this substring works is that I establish the start and then how long I want it to be from the start position.

How can I alter my query to just return them email only from the column.

Upvotes: 0

Views: 928

Answers (1)

Shaun Peterson
Shaun Peterson

Reputation: 1790

I agree with the above comments that this is not an elegant way of doing it but if you really need to use substring then have a look at the below.

I have changed this to work with oracle because that is what I have available and I am unsure what you are using but you should be able to get the idea from it.

SELECT substr(EmailJson, (instr(EmailJson,"Type":"Other"', 'ess":"')+6), (instr(EmailJson,"Type":"Other"', '","Type') - (instr(EmailJson,'ess":"')+6))) From Respondents;

Upvotes: 1

Related Questions