Substring in t-sql

Sorry if i put quotes around the numbers i get rid of the error, however it still doesn't work.It Brings back a null Value.

Let's see:

SUBSTRING(string, start, length)  means that you will get whatever is at start position in the string (starting with the "start-th" character)  having a length of 2, in your case the 298th character and the subsequent one (length of 2)

but if the string is shifted with only 1 position to the right ro to the left, if the string doesn't always have the exact same length and structure (date starting at exact same position) you will not get the expected result.

For this very reason in the example above you got from substring the "e6" value (it says in the error(, which you try to compare to 1 abd 9. You cannot compare a string, "e6" to numbers hence the error.

You need first to look for a sequence in the string that preceeds your days and is always the same an only than apply the substring. That is you need to parse the string.

A pattern in your string seems to be that the day always occurs after the "Bill Day " sequence and between the start of this sequence to the day value are always the same characters, 29 of them so insead of tf

SUBSTRING(history_text,298,2)

you shoul try:

SELECT substring(history_text, patindex('%Bill Day %' ,history_text)+29, 2)

one more thing, the day number will not always be 2 digits so the 2 parameter is not really goo because if the date is lets say 7 yo'll get error because you'll get bek 7&. We need to parse that day from the position it starts till the & character after it. so this will work:

SELECT substring(history_text,patindex('%Bill Day %',history_text)+29,patindex('%&%', right(history_text,len(history_text)-patindex('%Bill Day %',history_text)-28))-1)

Declare @SubStringSearch VarChar(200)
Set @SubStringSearch = 'Bill Day&nbsp;</td><td>&nbsp;'
Select substring(
            substring(history_text,
               (charindex(@SubStringSearch,history_text)+ len(@SubStringSearch))
            ,5)
       ,0,charindex('&',
            substring(history_text,
               (charindex(@SubStringSearch,history_text)+ len(@SubStringSearch))
            ,5)))
FROM customer_history

This will only work if the value proceeding the day integer is :'Bill Day&nbsp;</td><td>&nbsp;'

If this were to ever change you would have to modify the value of the @SubStringSearch variable but that would be the only change required.

ah very good, ok i am trying to understand your syntax how come the need for two charindex?

Also, what happens if i want to retreive the From Days?
I am trying to understand the syntax first to play around but if i want to get 17 and 4 from the histoey_text inserts how do i do this?
I'm sorry i should of asked for this at the start not just the 25 and 7.
By using the same synatx  do i just need to change the substringsearch, the problem there is that the first number is included in that and that will be differnt on ever row?

Hi Zberteoc,
I have tried to run your code also, the first one works but as you say it will bring back some unwanted data types, however when i run the second example it brings back an error:
Argument data type text is invalid for argument 1 of len function.
The history_text is of datatype Text, and i tried changing the len to datalength but it still returned an error,sorry about all these qustions, i am only startig out with SQL about 6 months now!
Thanks for you help so far.

Elaine.

The second CharIndex is getting the "&" after the Numbers, so you will get the Numeric Values regardless of the number of digits.

Ok -- Before I parse the second string, is there no possibility that instead of storing the HTML that you store the values in separate columns?

No unfortunitly not.
Only in the history table. This goes for a lot of changes such as product price changes or sales reps changes also.
I know this is not the best way of managing the info but i am pulling the information through replication into my data warehouse from a live  database created by someone esle, and i tried as best as possiable to follow their table lay out.

Thanks for you help so far, i really appriciate this.
Elaine.

<<Hi Zberteoc,
I have tried to run your code also, the first one works but as you say it will bring back some unwanted data types, however when i run the second example it brings back an error:
Argument data type text is invalid for argument 1 of len function.
>>

Replace len(history_text) with some big number like 10000.

Declare @SubStringSearch VarChar(200)
Declare @SecondaryStringSearch VarChar(200)

Set @SubStringSearch = 'Bill Day&nbsp;</td><td>&nbsp;'
Set @SecondaryStringSearch = '&nbsp;</td><td>&nbsp;'

Select history_id,customer_id, date_entered, substring(
            substring(history_text,
               (charindex(@SubStringSearch,history_text)+ len(@SubStringSearch))
            ,5)
       ,0,charindex('&',
            substring(history_text,
               (charindex(@SubStringSearch,history_text)+ len(@SubStringSearch))
            ,5))),

     
            substring(substring(
               substring(history_text,
               (charindex(@SubStringSearch,history_text)+ len(@SubStringSearch))
               ,Len(cast(history_text as VarChar(max)))
            ),charindex(@SecondaryStringSearch
                  , substring(history_text,
                  (charindex(@SubStringSearch,history_text)+ len(@SubStringSearch))
                  ,Len(cast(history_text as VarChar(max))))) + len(@SecondaryStringSearch),10
            ),0,

            charIndex('&',substring(
               substring(history_text,
               (charindex(@SubStringSearch,history_text)+ len(@SubStringSearch))
               ,Len(cast(history_text as VarChar(max)))
            ),charindex(@SecondaryStringSearch
                  , substring(history_text,
                  (charindex(@SubStringSearch,history_text)+ len(@SubStringSearch))
                  ,Len(cast(history_text as VarChar(max))))) + len(@SecondaryStringSearch),10
            )))
FROM customer_history

Ok, Elaine, this should work:

SELECT
            replace(                                                                              -- replace & with '' (empty string) if one digit day
                        substring
                        (
                              history_text,                                                      -- this is the text
                              patindex('%Bill Day&nbsp;%',history_text)+29,      -- this is the start point
                              2                                                                        -- this is the length
                        ), '&','') AS 'Day'
FROM
      #customer_history


I used it with the CREATE/INSERT examples you gave in your question and I got the good results: 25 and 7

Here without comments:

SELECT
      replace(
            substring
            (
                  history_text,
                  patindex('%Bill Day&nbsp;%',history_text)+29,
                  2
            ), '&','') AS 'Day'
FROM
      #customer_history

Thank you all for your help, i am going to use messen1975 answer as my solution as he was able to show me the From and To dates,however  i am unsure what to do about allocation the points Do you all agree if i choose the ' accept muliple solutions' Or because i am usng messen1975's answer  to allocate all the points to hm?
I don't want to offend anybody this is why i am asking this.
You have all been brilliant with your answers and times in responce.
Elaine.

Putoch, I am not selfish, but I gave you the explanations how and why you have to do plus the starting ideea, for you and all the subsequent answerers, of how to parse the text, not to mention that my previous post gave you a nice and simple solution that works.

I am only concern about my time I spent on this message. messen1975 was a good assist I would say.

Thats grand i completely understand, i will offer multiple solution points as i agree with you that everyone did infact give a correct answer and great detail and advise.

Thank you so much for all you help you are all great teachers!!!
Putoch

No, worries, if you put me as good assist is fine. I don't really lnow what the options are.