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®
View Solutions Only
DarrenJackson

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
DarrenJackson

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.

Register
anillucky31

Commented:
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

DarrenJackson

Author

Commented:
anillucky31  I would like them in separate columns

Thanks
Commented:
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

Anthony Perkins
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

anillucky31

Commented:
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.
Anthony Perkins
Top Expert 2012

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

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