T-Sql Instr query

DarrenJackson
DarrenJackson used Ask the Experts™
on
Guys I am trying to use the instr function in Microsoft SQL 2008 using t-sql im a bit rusty and struggling with this. I need to split up the following using the "-" as a splitter

WRKP-PRNT-WNDW

1st column would show
WRKP
Then second would show
PRNT
3rd column would show
WNDW

but there may be more than 3 potentially 4 or 5

Can you assist with the T-SQL

Regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
And to add to this sometimes there is just 1 value

like

WRKP

so it would need to just return this in the first column

Author

Commented:
Ive got this but it only returns the first value and it doesnt return any values if there is only 1 entry

LEFT(SUBSTRING(a.probcode + '-', CHARINDEX(' ', a.probcode + '-') +1, LEN(a.probcode + '-')),
              CHARINDEX('-', SUBSTRING(a.probcode+ '-',
              CHARINDEX('-', a.probcode + '-') +2, LEN(a.probcode + '-')))) as test,
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
is there a maximum?

I know how to split easily into rows:
http://www.experts-exchange.com/A_1536.html

you can use the function to put it back into columns, but only up to a given number of columns ...


select max(case when row_num = 1 then Value end col1
     , max(case when row_num = 2 then Value end col2
     , max(case when row_num = 3 then Value end col3
     , max(case when row_num = 4 then Value end col4
     --- etc ---
  from dbo.ParmsToList('WRKP-PRNT-WNDW', '-') f

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

can you give more details. it lookes like you have column  probcode. this column has data something like this WRKP-PRNT-WNDW

and result should look lkie this

WRKP PRNT WNDW

you want them to select them as column in your select query

Author

Commented:
anillucky31  I would like them in separate columns

Thanks
Here is solution for your problem (If i understood it correctly).

For this i have used two user defined function.

One function is Charindex2. I got this function from internet ( http://sqlhelper.wordpress.com/2011/05/04/sql-string-functions-substring-and-charindex/ ) and modified it.

This function returns the Nth Occurrence of a Character in a String.

I have written one function GetValueByIndex.  See the attached code for more details. this should work for your requirement

SELECT dbo.GetValueByIndex(a.probcode , '-', 0) AS Column0, dbo.GetValueByIndex(a.probcode , '-', 1) AS Column1,
dbo.GetValueByIndex(a.probcode , '-', 2) AS Column2, dbo.GetValueByIndex(a.probcode , '-', 3) AS Column3,
dbo.GetValueByIndex(a.probcode , '-', 4) AS Column4, dbo.GetValueByIndex(a.probcode , '-', 5) AS Column5
/*@InputString='WRKP-PRNT-WNDW-abc-def'
 @delimiterString = '-'
 @Index = 0 will return WRKP
 @Index = 1 will return PRNT
 ..
 ..
 otherwise it will return
 */
CREATE FUNCTION [dbo].[GetValueByIndex](@InputString varchar(8000), @delimiterString varchar(8000), @Index int) RETURNS VARCHAR(8000)

AS
BEGIN

	DECLARE @OutputValue VARCHAR(8000)
	SET @OutputValue = null

	IF(@Index = 0)
	BEGIN
		IF(dbo.charindex2(@delimiterString,@InputString, 1) = 0)
			SET @OutputValue = @InputString
		ELSE IF( dbo.charindex2(@delimiterString,@InputString, 1) > 0)
			SET @OutputValue = SUBSTRING(@InputString, 1, dbo.charindex2(@delimiterString,@InputString, 1) - 1 )
	END
	ELSE
	BEGIN
		IF (dbo.charindex2(@delimiterString,@InputString, @Index) > 0 AND dbo.charindex2(@delimiterString,@InputString, @Index+1) = 0)
			SET @OutputValue =SUBSTRING(@InputString, dbo.CHARINDEX2(@delimiterString,@InputString, @Index) + 1, LEN(@InputString) )
		ELSE IF ( dbo.charindex2(@delimiterString,@InputString, @Index+1) > 0)
			SET @OutputValue =SUBSTRING(@InputString, dbo.CHARINDEX2(@delimiterString,@InputString, @Index) +1, dbo.CHARINDEX2(@delimiterString,@InputString, @Index+1) -dbo.CHARINDEX2(@delimiterString,@InputString, @Index)-1  )	
	END 	
	 
	
  

	RETURN(@OutputValue)
END

Open in new window

create FUNCTION [dbo].[CHARINDEX2](@TargetStr varchar(8000), @SearchedStr varchar(8000), @Occurrence int) RETURNS int

AS
BEGIN

DECLARE @pos int, @counter int, @ret INT
SET @ret = 1

SET @pos = CHARINDEX(@TargetStr, @SearchedStr)
SET @counter = 1

IF @Occurrence = 1 SET @ret = @pos

ELSE
BEGIN
WHILE (@counter < @Occurrence AND @ret > 0)
BEGIN
SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
SET @counter = @counter + 1
SET @pos = @ret
END
END

RETURN(@ret)
END

Open in new window

Top Expert 2012
Commented:
Or simply as below:
SELECT	PARSENAME(REPLACE(a.probcode, '-', '.'), 3),
		PARSENAME(REPLACE(a.probcode, '-', '.'), 2),
		PARSENAME(REPLACE(a.probcode, '-', '.'), 1)

Open in new window

will PARSENAME function work on string which has more than 4 parts? As far as i know it will not work if there are more than 3 dots in string.  please correct me if i am wrong.
Top Expert 2012

Commented:
>> please correct me if i am wrong. <<
You are absolutely right.  I overlooked the requirement for 5 parts.

Author

Commented:
Guys thanks for the help anillucky3 your suggestion is spot on this does the trick acperkins your suggestion also work but as per the other posts I belive there is a limitation in this.

I want to award you both points for helping me

Regards

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial