Splitting a Sentence and select only required string in sql

Question

I have column in my table named billing_cycle which contains only below given two strings and i need to retrieve only monthly and Quarterly from that string.

Using the below query i can fetch string Quarterly properly but not string monthly.In the out put the word A overlapping with the string Monthly.It can be replaced using case and stuff command but i dont want to use those. I would like to get the output using substring and charindex or with any other function.

SELECT substring('208-Billing Period - Quarterly Average Daily Balance', charindex('- ', '208-Billing Period - Quarterly Average Daily Balance') + 2, 9)


SELECT substring('210-Billing Period - Monthly Average Daily Balance', charindex('- ', '210-Billing Period - Monthly Average Daily Balance') + 2, 9)

Anyone could help me?

What is it you need help with? What isn't working in the SQL above? What are you expected results? — Thom A, Aug 20 '20 at 15:48
expected result is that it would only display monthly instead of monthly A. — Rajeev, Aug 20 '20 at 15:51
Hi Larnu,thanks for ur suggestion .Actually i need to use this query for a column and that column contain only these two strings '208-Billing Period - Quarterly Average Daily Balance' And ' 210-Billing Period - Monthly Average Daily Balance ' so i need to use only one query to fetch data from that column.If i put 7 as you said then the word 'quarterly' would not display properly.hope u get me!! — Rajeev, Aug 20 '20 at 16:05
The above are literal strings, not a column. So are you getting this data from a table instead? — Thom A, Aug 20 '20 at 16:10
yes .Below is the query from table. select substring([billing cycle],charindex('- ',[billing cycle])+2,9) from feeschedule_data.The result is Quarterly , Monthly A so i need to remove A from the result set.But On the same time i need to get the string Quarterly properly.Just want to inform that billing cycle that column contain only these two strings '208-Billing Period - Quarterly Average Daily Balance' And ' 210-Billing Period - Monthly Average Daily Balance ' — Rajeev, Aug 20 '20 at 16:17
Please don't dumb down the problem. If you are querying data in a table, show the table, show the data, show the query, show the desired results. — Aaron Bertrand, Aug 20 '20 at 16:17
`like` could be used in a `case` to make this really simple and much easier SQL to read and comprehend what its doing. — vvvv4d, Aug 20 '20 at 16:22
Hi @AaronBertrand,i have placed now all details properly in the above statement — Rajeev, Aug 20 '20 at 16:26
Those details go in the question, please. Users are answering questions, not assembling details from comment threads. Also please explain what you expect to happen when (a) a string contains neither `Monthly` nor `Quarterly`, or (b) a string contains _both_ `Monthly` _and_ `Quarterly`. — Aaron Bertrand, Aug 20 '20 at 16:28

score 2 · Answer 1 · answered Aug 20 '20 at 16:21

2

One method would be a couple of nested CHARINDEX functions. I do this is the FROM as it avoids repetition of expressions:

SELECT SUBSTRING(V.YourColumn,CI1.I, CI2.I - CI1.I)
FROM (VALUES('208-Billing Period - Quarterly Average Daily Balance'),('210-Billing Period - Monthly Average Daily Balance'))V(YourColumn)
     CROSS APPLY (VALUES(CHARINDEX('- ',V.YourColumn)+2))CI1(I)
     CROSS APPLY (VALUES(CHARINDEX(' ',V.YourColumn,CI1.I)))CI2(I);

answered Aug 20 '20 at 16:21

Thom A

88,727
11
45
75

1

Sure, would you like to do the same with your question, @Rajeev ? If not, you should be able to easily work out how to amend the names of the column, I did aptly name it "`YourColumn`" – Thom A Aug 20 '20 at 16:58

vvvv4d · Answer 2 · 2020-08-20T17:24:20.283

I recommend you make use of like and a case to simplify this so you don't have to hardcode specific position values into substring

  select
    case when v.Value like '%Monthly%'
        then 'Monthly'
        when v.Value like '%Quarterly%'
        then 'Quarterly'
    end as [Type],
    v.Value
from (
    select '210-Billing Period - Monthly Average Daily Balance' as [Value]
    union select '208-Billing Period - Quarterly Average Daily Balance'
) V

Results:

score 0 · Answer 3 · answered Aug 21 '20 at 14:50

It is much easier using CASE/LIKE etc., but as you are not going to use those (is it a homework?), instead of having 9 at end of the substring(), you will have to use the position - 1 of the next space after the word Quarterly or Monthly. I have left some additional columns to help you understand it.

declare @test table
(
ID int identity(1,1)
,Billing varchar(100)
)

insert into @test(Billing)
select '208-Billing Period - Quarterly Average Daily Balance' UNION
select '210-Billing Period - Monthly Average Daily Balance'

select * 
,charindex('- ', Billing) + 2 position_of_first_dash
,substring(Billing, charindex('- ', Billing) + 2, LEN(Billing)) substring_from_first_dash_pos_plus_2
,charindex(' ', substring(Billing, charindex('- ', Billing) + 2, LEN(Billing))) pos_first_space_after_dash_word
,substring(Billing, charindex('- ', Billing) + 2, charindex(' ', substring(Billing, charindex('- ', Billing) + 2, LEN(Billing)))-1 )
from 
@test

Result returned is:

Splitting a Sentence and select only required string in sql

3 Answers3