How to use IF or case with charindex? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to use IF or case with charindex?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-18 : 08:28:12

Hi
I have a column col1 in a table having below strings. How do I get only the required part of the string as I explaind below. (Needed strings are followed by i.e as below).

txtString=duracell+alkaline+battery+&submit1=Search&OVR31&OVKWID=24137211531
i.e : duracell+alkaline+battery+

txtString=sealed+lead+acid+batteries+&submit&gclid=CMekn-fesZAodpzIcHA
i.e : sealed+lead+acid+batteries+

ID=3&Department=
i.e : 3

ID=22893
i.e : 22893

lngSessionId=837541253&pid=22714
i.e : 22714

CCcode=502&OrderId=INT10350&puramt=1773
i.e : INT10350

tduid=5c14526847651e9054552acc134e9a84&url=http://www.somedomain.co.uk/proddetails.asp?id=4204
i.e : 4204

Code I am using (Got this one from my other posting)

declare @QueryString nvarchar(255)

select @QueryString = 'ID=23456&Depatment='

select substring(@QueryString, charindex('ID=', @QueryString) + 3,
charindex('&', @QueryString) - charindex('ID=', @QueryString) - 3) from Pageviews

1. But the above code only works if the string starts with 'ID='.
2. Because the col1 has strings starts with different letter this code fail to excute and gives error
3. Pathindex with regular express '(patindex('%[^0-9]%',v)-1)' cannot be used because it gets anynumber
in the string - string has mixer of numbers and letters

SO IF ANYONE LET ME KNOW HOW TO USE IF or CASE with 'charindex' or any other method to get the above said result would be greatfull.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-18 : 08:31:08

Make use of the function I provided http://sqlteam.com/forums/topic.asp?TOPIC_ID=94246

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-18 : 09:53:42

quote:
Originally posted by madhivanan

Make use of the function I provided http://sqlteam.com/forums/topic.asp?TOPIC_ID=94246

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-18 : 09:55:21

quote:
Originally posted by Vaishu

quote:
Originally posted by madhivanan

Make use of the function I provided http://sqlteam.com/forums/topic.asp?TOPIC_ID=94246

Madhivanan

Failing to plan is Planning to fail

What is your comment?

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-18 : 10:07:37

Hi Madhi

Every time I post, the session expires and ending up post nothing. Sorry for that.

Thanks a lot. Seems that code works. But how do I read the column from the table
(Column name QueryString, Table Name Pageviews). Do I have to edit the function or in the select command (pasted below)

dbo.get_text('txtString=duracell+alkaline+battery+&submit1=Search&OVR31&OVKWID=24137211531','txtString'),
dbo.get_text('txtString=sealed+lead+acid+batteries+&submit&gclid=CMekn-fesZAodpzIcHA','txtString'),
dbo.get_text('ID=3&Department=','ID'),
dbo.get_text('lngSessionId=837541253&pid=22714','pid'),
dbo.get_text('CCcode=502&OrderId=INT10350&puramt=1773','orderid'),
dbo.get_text('tduid=5c14526847651e9054552acc134e9a84&url=http://www.somedomain.co.uk/proddetails.asp?id=4204','?id')

As this is log files I like to do everything in SQL .Then use ASP.Net to display some other results,graph, chart etc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-19 : 01:01:34

You dont need to change the function. You need to change the select statement

Select dbo.get_text(your_column,'Search_string') from yourtable

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-19 : 04:40:12

Hi

THANK YOU VERY MUCH

quote:
Originally posted by madhivanan

You dont need to change the function. You need to change the select statement

Select dbo.get_text(your_column,'Search_string') from yourtable

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-19 : 12:55:48

Hi

http://www.google.com/search?rlz=1T4SUNA_en___GB204&hl=en&q=
FUJI+ACCESSORIES&btnG=Google+Search

1.I am trying to get only the keywords from the visiting website to our website. So how do I get only 'FUJI+ACCESSORIES' from the above string. I am using the below function but the result I am getting is
FUJI+ACCESSORIES&btnG=Google+Search.

Note : 90 % of the keyword starts from a word + word + word& (ending with &). So Is any way to get the key words like below

word + word + word

Function
CREATE FUNCTION [dbo].[referer_keyword] (@string varchar(4000), @search_string varchar(2000))
RETURNS varchar(100) AS
BEGIN
Return
(
SELECT SUBSTRING(Referer, CHARINDEX(@search_string, Referer)+len(@search_string)+1,
CHARINDEX('&', Referer)-len(@search_string)-2) AS Referer
FROM (select @string+case when @string like '%&%' then ''
--when @string like '%http%' then '&'
else '&' end as Referer) as t
)
end

Select Command

Case when referer LIKE 'http%' Then
dbo.referer_keyword(referer,'&q')
when referer LIKE '%?q%' Then
dbo.referer_keyword(referer,'?q')
else referer
end as KeyWords

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 13:04:14

Also see this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94481

E 12°55'05.25"
N 56°04'39.16"

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-20 : 05:33:14

Hi Peso

The example link you gave is not works for me because the string I am getting is different every time. The function I am using is works but it is taking all letters after the search string.

ex: search string for the below line is '?form_keyword'. Expected result is 'fujifilm%20finepix%20f10'
but I am getting as below

fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1

http://www.pricabber.co.uk/search.php?form_keyword=fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1

quote:
Originally posted by Peso

Also see this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94481

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 06:25:51

declare @v varchar(1000)
set @v='fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1'
select left(@v,charindex('&',@v)-1)

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-20 : 07:18:47

Hi Madhi

Original string is below

http://www.pricabber.co.uk/search.php?form_keyword=fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1

I am using the function you posted for my previous threads (works fine for the colum I wanted, now I am working on the other column to get only the key words or search string typed)

RESULT I AM GETTING IS 'fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1'

RESULT WANTED IS 'fujifilm%20finepix%20f10'

I have tried your code, its giving me the string before the & - WHICH IS THE FIRST '&' from the original string. So how do I alter the function below to achive the above said results

quote:
CREATE FUNCTION [dbo].[get_text] (@string varchar(4000), @search_string varchar(20))
RETURNS varchar(100) AS
BEGIN
Return
(
SELECT SUBSTRING(col2, CHARINDEX(@search_string, col2)+len(@search_string)+1,
CHARINDEX('&', col2)-len(@search_string)-2) AS col2
FROM (select @string+case when @string like '%&%' then ''

else '&' end as col2) as t
)
end

quote:
Originally posted by madhivanan

declare @v varchar(1000)
set @v='fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1'
select left(@v,charindex('&',@v)-1)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 07:28:39

Will this do?

-- Prepare sample data
DECLARE	@Test VARCHAR(200)

SET	@Test = 'http://www.pricabber.co.uk/search.php?form_keyword=fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1'

DECLARE	@Sample TABLE ([Text] VARCHAR(200))

INSERT	@Sample
SELECT	@Test

-- Show the expected output 1
SELECT	[Text],
	CASE
		WHEN 0 IN (StartPos, EndPos) THEN NULL
		ELSE SUBSTRING([Text], StartPos + 8, EndPos - StartPos - 8)
	END AS Excerpt
FROM	(
		SELECT	@Test AS [Text],
			CHARINDEX('keyword=', @Test) AS StartPos,
			CHARINDEX('&mode=', @Test) AS EndPos
	) AS d

-- Show the expected output 2
SELECT	[Text],
	CASE
		WHEN 0 IN (StartPos, EndPos) THEN NULL
		ELSE SUBSTRING([Text], StartPos + 8, EndPos - StartPos - 8)
	END AS Excerpt
FROM	(
		SELECT	[Text],
			CHARINDEX('keyword=', @Test) AS StartPos,
			CHARINDEX('&mode=', @Test) AS EndPos
		FROM	@Sample
	) AS d

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 09:13:13

and using the function I posted

select left
	(
	dbo.get_text('http://www.pricabber.co.uk/search.php?form_keyword=fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1','?form_keyword')
	,charindex('&',dbo.get_text('http://www.pricabber.co.uk/search.php?form_keyword=fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1','?form_keyword'))-1
	)

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-20 : 10:05:41

Hi

Thanks for the reply. I am trying 'Peso's code into the function below but getting sysntax error. Could please let me know where I am making mistake

'referer' in this function is the column name of the table

quote:
CREATE FUNCTION [dbo].[referer_keyword] (@string varchar(4000), @start_string varchar(40), @end_string varchar(40))
RETURNS varchar(100) AS
BEGIN
Return
(
SELECT [referer],
CASE
WHEN 0 IN (StartPos, EndPos) THEN NULL
ELSE SUBSTRING([referer], StartPos + 8, EndPos - StartPos - 8)
END AS Excerpt
FROM (
SELECT @string AS [referer],
CHARINDEX(@start_string, @string) AS StartPos,
CHARINDEX(@end_string,@string) AS EndPos
) AS D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 10:14:59

[code]CREATE FUNCTION dbo.fnRefererKeyword
(
@String VARCHAR(8000),
@From VARCHAR(40),
@End VARCHAR(40)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @FromPos SMALLINT,
@ToPos SMALLINT

SELECT @FromPos = CHARINDEX(@From, @String),
@ToPos = CHARINDEX(@End, @String)

RETURN CASE
WHEN 0 IN (@FromPos, @ToPos) THEN NULL
ELSE SUBSTRING(@String, @FromPos + LEN(@From), @ToPos - @FromPos - LEN(@From))
END
END[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 10:17:40

[code]-- Prepare sample data
DECLARE @Sample TABLE ([Text] VARCHAR(200))

INSERT @Sample
SELECT 'http://www.pricabber.co.uk/search.php?form_keyword=fujifilm%20finepix%20f10&mode=g_uk_b_s&skd=1'

-- Show the expected output
SELECT [Text],
dbo.fnRefererKeyword([Text], 'keyword=', '&mode=')
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-20 : 11:28:52

Hi

Thanks a Lot Madhivanan and Peso for great help. HAPPY HOLIDAYS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-21 : 01:22:20

quote:
Originally posted by Vaishu

Hi

Thanks a Lot Madhivanan and Peso for great help. HAPPY HOLIDAYS

Good Luck

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources