get string after specified character in column? 1

[ahnugroho]

Hi all,

I have these data in table:
record no 1 :~C~M~I~P010~004~ABC~N~FNSH
record no 2 :~C~~I~P32 ~002~CUSTOM~N~FNSH

I am stack, how to get "P32" and "P010"?
I tried using SUBSTRING in SQL statement.
SELECT SUBSTRING(colPartName,8,4)
FROM tblTransacAudit

it's returns: "P010" and "32 ~"

Does anyone know to get string after count character "~" is 3 ?

thanks
nugroho

 

[dbomrrsm]

as long as there is no occurance of P before the P in what you want

select substring(':~C~~I~P32 ~002~CUSTOM~N~FNSH',
patindex('%P%',':~C~~I~P32 ~002~CUSTOM~N~FNSH'),4)

select substring(':~C~M~I~P010~004~ABC~N~FNSH',
patindex('%P%',':~C~M~I~P010~004~ABC~N~FNSH'),4)

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[vongrunt]

Get such data out of database, parse tokens (each delimited with ~), then put tokens back - one in each separated field.

Much less trouble later and much easier to do overall. For VB example: FifthToken = Split( myData, "~" )(4).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[r937]

vongrunt, that would require shipping the entire table over to vb, yes?

this wouldn't be too bad if all rows were always required, but what if you were looking for only P32 in the WHERE clause?

yes, you'd probably get a table scan in any case, but shipping only the P32 rows out of the table has gotta be easier than shipping the entire table and letting the vb code do the WHERE filtering

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Erics44]

can you use the charindex function

charindex('~' , strSearch) + 3

This could work

 

[vongrunt]

this wouldn't be too bad if all rows were always required, but what if you were looking for only P32 in the WHERE clause?

These two sample values are basically serialized "rows" with tilde char (~) as column separator. Substrings "P010" and "32 ~" are 5th "columns" within rows. There is a good chance this parsing requirement ("... get 5th column") is not the last one, in which case one-time tokenization/atomization outside DB is way better choice - both syntax and performance-wise.


Other than that, personally I'd write all-around UDF like:

create function dbo.getNthToken( @str varchar(1000), @delim varchar(8), @tokenNo tinyint )
returns varchar(64)
as
begin
	declare @ret varchar(64)
	declare @lastpos smallint
	declare @pos smallint; set @pos = 1-len(@delim)

	while  @pos < len(@str) and @tokenNo > 0
	begin
		set @lastpos = @pos + len(@delim)
		set @pos = charindex(@delim, @str, @lastpos)

		if @pos = 0 set @pos = len(@str)
		set @tokenNo = @tokenNo - 1
	end

	if @tokenNo=0 set @ret = substring(@str, @lastpos, @pos-@lastpos)
	return @ret
end
go

... then use it where necessary:

-- single-value example
select dbo.getNthToken('~C~~I~P32 ~002~CUSTOM~N~FNSH', '~', 5)

-- table query example
select dbo.getNthToken(myColumn, '~', 5), blah blah
from myTable

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[ahnugroho]

Hi all,

vongrunt, you're right about serialized rows.
it's Audit trailer table and each column is separated with char ("~").

your code is working fine in my SQL Server and I can get the value what i want.

I have an old application (Access 2K) to retrieve the same table data from SQL 2k.

I use this code in Access module:

Public Function GetString(sInput, nLoc) As String
Dim nLen As Long
Dim nPos As Long
Dim nCnt As Long
Dim i As Long

Dim sCut As String

nLen = Len(sInput)
i = 1
Do While i < nLen
nPos = InStr(i, sInput, "~", vbBinaryCompare)
If nPos > 0 And nPos <> 1 Then
If nPos >= i Then
sCut = Mid(sInput, i, IIf(nPos - i = 0, 1, nPos - i))
If sCut = "~" Then sCut = ""
nCnt = nCnt + 1
Else
nCnt = nCnt + 1
sCut = ""
End If
If nCnt = nLoc Then
GetString = sCut
Exit Do
End If
End If
i = nPos + 1
Loop

End Function

it's working on Acces 2k but I am stack convert to T-SQL on SQL2K.

anyway, thank u for your code, and get your STARS.

ahnugroho

 

[vongrunt]

One problem: UDF I provided parses last token incorrectly - when it has no delimiter:

-- returns FNS instead of FNSH
select dbo.getNthToken('~C~~I~P32 ~002~CUSTOM~N~FNSH', '~', 9)

Here is the fix:

...
	begin
        set @lastpos = @pos + len(@delim)
        set @pos = charindex(@delim, @str, @lastpos)

        if @pos = 0 set @pos = len(@str)[b]+len(@delim)[/b]
        ...

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba