Use String Function

Question

Use String Function

shuzi

Say Hey Kid

Points: 684
More actions
September 8, 2008 at 9:55 am

#71083

I would like to retreive the last string from this pattern
,B110,A104,AUSL,
,L101,A101,01,
which means i want to retrieve AUSL and 01
thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

will you columns always end in ',' ?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

shuzi Say Hey Kid Points: 684 More actions · Answer 2

yes,

here is the pattern we have, i need to retrieve the right most field

,,,01,

,B110,A104,AUSL,

,B135,,DEN,

,,,03,

,,,AZ,

,,,MALA,

,,,BUR,

thanks

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

HI there,

Here is my solution but I have a feeling there is better one I'll keep working at it:

DECLARE @Tmp VARCHAR(100)

SET @Tmp = ',ad,af,ag,ah,bb,'

SELECT SUBSTRING(@tmp,LEN(@tmp) -

PATINDEX('%,%',REVERSE(LEFT(@tmp,LEN(@tmp)-1)))+1,

PATINDEX('%,%',REVERSE(LEFT(@tmp,LEN(@tmp)-1)))-1)

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi Chris

It's a kinda cross-post, Matt is dealing with it too:

http://www.sqlservercentral.com/Forums/Topic565481-8-1.aspx

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Thanks Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Reverse would give the correct results assuming the patterns are close enough. Tally table is another option.

I'd probably go with a CLR function though (would likely be faster in this case, since we're dealing with string ops vs a REVERSE function, which is kind of a dog perf-wise)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

shuzi Say Hey Kid Points: 684 More actions · Answer 7

some other option

DECLARE @string varchar(200)

SET @string = ',B110,A104,AUSL,'

SELECT

REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',',REVERSE(@string),2)-2))

SET @string = ',L101,A101,01,'

SELECT

REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',',REVERSE(@string),2)-2))

shuzi Say Hey Kid Points: 684 More actions · Answer 8

shuzi

Say Hey Kid

Points: 684

September 8, 2008 at 11:50 am

#868637

can you tell me what tally table is? thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

shuzi (9/8/2008)
can you tell me what tally table is? thanks

Sure - here's a good article on it:

http://www.sqlservercentral.com/articles/TSQL/62867/[/url]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

shuzi Say Hey Kid Points: 684 More actions · Answer 10

so how to retrieve the middle value, e.g. A104 and A101

thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

shuzi (9/9/2008)
so how to retrieve the middle value, e.g. A104 and A101
thanks

The middle value of how many values? Will it always be three?

Looks like this is going to be a tally-table job, as Matt suggests:

DROP TABLE #Temp

CREATE TABLE #Temp (RowID int, String VARCHAR(20))

INSERT INTO #Temp (RowID, String)

SELECT 1, ',B110,A104,AUSL,' UNION ALL

SELECT 2, ',L101,A101,01,'

DECLARE @Delimiter VARCHAR(20)

SET @Delimiter = ','

SELECT t.RowID, number, SUBSTRING(t.String+@Delimiter, number,

CHARINDEX(@Delimiter, t.String+@Delimiter, number) - number)

FROM Numbers n, #Temp t

WHERE number <= LEN(REPLACE(t.String,' ','|'))

AND SUBSTRING(@Delimiter + t.String, number, LEN(REPLACE(@Delimiter,' ','|'))) = @Delimiter

ORDER BY RowID, number

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

shuzi Say Hey Kid Points: 684 More actions · Answer 12

shuzi

Say Hey Kid

Points: 684

September 9, 2008 at 9:16 am

#869120

no, it varies with the length

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Then use the starting point Chris gave you with the Tally, and use that as a "base table", excluding the first and last "new rows" based on the initial rowID. Once you do that - you have all of your "middle values".

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

shuzi Say Hey Kid Points: 684 More actions · Answer 14

I tried this but it gives me error Number is invalid table