0

I have the value for the column in a table and i need to exact some part of the string

 baf93b64-c255-4dda-b9dc-3f7438b49335-mkttrg&utm_source=bing&utm_medium=cpc&utm_campaign=MO+-+Payday&utm_term=payday+loan&utm_content=Payday+Loans+(Phrase)

now I have to extract from the first & i.e., 

utm-source = bing
utm-medium  = cpc 
utc_campaign  = MO+Payday
utm_term = 'payday+loan'
utm_content = Payday+loans+(Phrase).

can you please help me with the sub string function to extract these parts from the column mentioned value.

Thanks in advance.

kris
  • 39
  • 1
  • 1
  • 5

2 Answers2

1

Use substring with the start at the charindex, and number of character extract is the length of the string subtract the length of the two pieces from the beginning and the end. You will need to do some data validation so that the col is of the form you want.

substring(col, charindex('&', col) + 1, len(col) - charindex('&', reverse(col)) - charindex('&', col))

Test code:

DECLARE @col nvarchar(100) = '12&12545643&euwpo';

SELECT substring(@col, charindex('&', @col) + 1, len(@col) - charindex('&', reverse(@col)) - charindex('&', @col))
DVT
  • 3,014
  • 1
  • 13
  • 19
  • it is throwing an error..Msg 537, Level 16, State 3, Line 16 Invalid length parameter passed to the LEFT or SUBSTRING function. –  kris Nov 29 '16 at 18:50
  • 1
    There is definitely enough here to give you a solid idea how to proceed. – Neo Nov 29 '16 at 18:52
  • @MisterPositive Thanks. – DVT Nov 29 '16 at 18:54
  • it is working for a one string but i need to pull for the entire table. –  kris Nov 29 '16 at 18:58
  • @kris Then you should search for the term "t-sql split string". There are a lot of resource on the web about this subject. And I think the title is somewhat misleading. – DVT Nov 29 '16 at 19:00
  • and in the string what i have mentioned in my question, it is not picking the last part "utm_content=Payday+Loans+(Phrase)" as it doesnt have the & at the end i guess!! –  kris Nov 29 '16 at 19:00
  • @DVT in the string what i have mentioned in my question, it is not picking the last part "utm_content=Payday+Loans+(Phrase)" as it doesnt have the & at the end i guess!! –  kris Nov 29 '16 at 19:11
  • @kris Check this http://stackoverflow.com/questions/10914576/t-sql-split-string It is similar to your question, except you use '&' and it use ','. – DVT Nov 29 '16 at 19:12
0

The main approach of solving this issue via using SplitString function.

Then depends on your comment:

in the string what i have mentioned in my question, it is not picking the last part "utm_content=Payday+Loans+(Phrase)" as it doesnt have the & at the end i guess!!

Use the following Code:-

SELECT top ((select count (*)  FROM dbo.SplitString('baf93b64-c255-4dda-b9dc-3f7438b49335-mkttrg&utm_source=bing&utm_medium=cpc&utm_campaign=MO+-+Payday&utm_term=payday+loan&utm_content=Payday+Loans+(Phrase)', '&')
where item like '%=%' ) -1) item
FROM dbo.SplitString('baf93b64-c255-4dda-b9dc-3f7438b49335-mkttrg&utm_source=bing&utm_medium=cpc&utm_campaign=MO+-+Payday&utm_term=payday+loan&utm_content=Payday+Loans+(Phrase)', '&')
where item like '%=%'

Result:-

enter image description here

ahmed abdelqader
  • 3,409
  • 17
  • 36
  • It is not working. getting an error..Msg 208, Level 16, State 1, Line 26 Invalid object name 'dbo.SplitString'. –  kris Nov 29 '16 at 19:28
  • Go to the link that mentioned in my answer and execute the SplitString first ... this is the link > http://www.aspsnippets.com/Articles/Split-and-convert-Comma-Separated-Delimited-String-to-Table-in-SQL-Server.aspx – ahmed abdelqader Nov 29 '16 at 19:29