SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 string formatting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skysyb
Starting Member

United Kingdom
16 Posts

Posted - 04/14/2008 :  06:58:27  Show Profile  Reply with Quote
Hi, I've a string like :

'...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,'

I'd like to know if there is a way to replace everything between these two integers (well, actually we can say two words as they are in the string format) with a single comma (i.e ,) and remove everthing leading and trialing to these two numbers please ?

In short , I would like to see the above line after string manipulation as follows :

'123456789,876564532'

Thanks for your help.

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/14/2008 :  07:01:35  Show Profile  Visit nr's Homepage  Reply with Quote
What are your rules - I assume these aren't fixed strings otherwise you could hard code the result.Sounds a bit like you want two entries out of a csv string with the second up to the first non numeric character.

In that case use a cte to break up the csv string and patindex to find the first non numeric character in the second string.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  07:06:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
How about this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/14/2008 :  08:35:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

skysyb
Starting Member

United Kingdom
16 Posts

Posted - 04/14/2008 :  14:02:47  Show Profile  Reply with Quote
Hi All, Thanks for all these answers, although they seem to be complicated for me (atleast for me really). Perhaps, if I should be able to validate what's in the string and throw an error , if the @str has any ascii values not in between 48 & 57 (i.e numbers) and not in (44, 32 : i.e a comma, or a blank space).

is there a way that we could do this please ? Thanks for ur help again !!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/14/2008 :  14:06:37  Show Profile  Reply with Quote
Have a look at CHARINDEX function in BOL
Go to Top of Page

skysyb
Starting Member

United Kingdom
16 Posts

Posted - 04/14/2008 :  14:19:57  Show Profile  Reply with Quote
i've tried this and it appears working fine. any other ideas to cut the length of the code or any system functions which I probably might not aware yet pls ?

declare @mypos int
select @mypos = 1
while (@mypos) <= datalength(@mystr)
begin
if ascii(substring(@mystr, @mypos, 1)) between 48 and 57 or ascii(substring(@mystr, @mypos, 1)) in (32,44)
print "excepted string"
else
print "not expected string"
break
end

btw, once again thanks so much for all of you for your prompt help !! truly appreciated.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/14/2008 :  14:48:58  Show Profile  Visit nr's Homepage  Reply with Quote
Are you just trying to
if the @str has any ascii values not in between 48 & 57 (i.e numbers) and not in (44, 32 : i.e a comma, or a blank space).

then
if patindex('%[^0-9, ]%', @mystr) <> 0
print 'invalid chr'
else
print 'good string'

This is just checking for invalid characters though which doesn't seem to be the original question.

What are you actually trying to do?


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 04/14/2008 14:50:13
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  16:10:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Here is a slight variation. First create this function
CREATE FUNCTION dbo.fnExtractDigitSequence
(
	@ColumnDelimiter CHAR(1) = ',',
	@Filter VARCHAR(8000) = '[0-9]',
	@UserData VARCHAR(8000) = NULL
)
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE	@Position INT,
	@ValidChar TINYINT,
	@LastValidChar TINYINT

SELECT	@Position = LEN(@UserData),
	@LastValidChar = 0

WHILE @Position > 0
	SELECT	@ValidChar =	CASE
					WHEN SUBSTRING(@UserData, @Position, 1) COLLATE Latin1_General_BIN LIKE @Filter THEN 1
					ELSE 0
				END,
		@UserData =	CASE
					WHEN @ValidChar = 1 THEN @UserData
					WHEN @LastValidChar = 0 THEN STUFF(@UserData, @Position, 1, '')
					ELSE STUFF(@UserData, @Position, 1, @ColumnDelimiter)
				END,
		@LastValidChar = @ValidChar,
		@Position = @Position - 1

	RETURN	CASE
			WHEN @UserData LIKE @ColumnDelimiter + '%' THEN SUBSTRING(@UserData, 2, 7999)
			ELSE NULLIF(@UserData, '')
		END
END
And then use with like this
SELECT	dbo.fnExtractDigitSequence(default, '[0-9]', '...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,')
SELECT	dbo.fnExtractDigitSequence(',', default, '...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,')
SELECT	dbo.fnExtractDigitSequence(default, default, '...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,')

SELECT	dbo.fnExtractDigitSequence(',', '[0-9]', Col1)
FROM	Table1
Output is
123456789,876564532

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

Edited by - SwePeso on 04/14/2008 16:13:50
Go to Top of Page

skysyb
Starting Member

United Kingdom
16 Posts

Posted - 04/15/2008 :  06:15:38  Show Profile  Reply with Quote
Thanks to all again for ur supportive responses. I am really glad to see thoughts being shared, resolving queries and encouraging people to feel free to post their questions !! truly appreciated.

btw nr, sorry for the confusion. Technically my task is to take multiple intigers in a string format and handle them in the batch job. But, inorder to handle them properly I would only need these integers be separated by commas and not by anything else. As you know, when we are accepting a string value through (n)(var)char datatype, people can ironically give anything including controle charecters and that makes my job difficult. so I figured I would ask a question in this forum and get some help. Sorry for the confusion again..
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/15/2008 :  12:41:03  Show Profile  Visit nr's Homepage  Reply with Quote
You mean you want all the integer values that are in the string?
so if you received
qwe,wqer,qwe,ew12356yui231iy3,,,123,123123tuytyut,,,123
you would want
123562313,123,12312,123


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

skysyb
Starting Member

United Kingdom
16 Posts

Posted - 04/15/2008 :  15:11:55  Show Profile  Reply with Quote
Yep, that absolutely correct - nr. that's exactly what I want the end result.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/16/2008 :  03:06:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by skysyb

Yep, that absolutely correct - nr. that's exactly what I want the end result.



Then refer the link I posted. With small change, it can be done

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/16/2008 :  03:15:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I thought the function posted 04/14/2008 : 16:10:12 did all that?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/16/2008 :  05:14:36  Show Profile  Visit nr's Homepage  Reply with Quote
Nope - it doesn't get rid of non numeric characters just terminates at the first non numeric.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/16/2008 :  05:28:41  Show Profile  Visit nr's Homepage  Reply with Quote
I would go for something like this
I like to keep splitting the string separate from updating the values for ease of maintenance.
And working with a table is usually more flexible.

create function ParseString
(@s varchar(8000))
returns varchar(8000)
as
begin
declare @t table (s varchar(1000))
declare @res varchar(1000)

-- Split the string
;with csvtbl(i,j, s) as
(select i=1, s=charindex(',',@s+','), substring(@s, 1, charindex(',',@s+',')-1)
union all
select i=j+1, j=charindex(',',@s+',',j+1), substring(@s, j+1, charindex(',',@s+',',j+1)-(j+1)) from csvtbl where charindex(',',@s+',',j+1) <> 0)
insert @t (s)
select s from csvtbl
where s like '%[0-9]%'

-- remove non numeric chars
while @@rowcount > 0
update @t
set s = replace(s, substring(s, patindex('%[^0-9]%', s), 1), '')
where patindex('%[^0-9]%', s) <> 0

-- concatenate values
select @res = coalesce(@res +',', '') + s
from @t
return @res
end
go

select dbo.ParseString('qwe,wqer,qwe,ew12356yui231iy3,,,123,123123tuytyut,,,123')
123562313,123,123123,123


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/16/2008 :  06:19:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by nr

Nope - it doesn't get rid of non numeric characters just terminates at the first non numeric.

I tested the function again and all these three variations

SELECT dbo.fnExtractDigitSequence(default, '[0-9]', '...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,')
SELECT dbo.fnExtractDigitSequence(',', default, '...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,')
SELECT dbo.fnExtractDigitSequence(default, default, '...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,')

returns same value, ie

123456789,876564532



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/16/2008 :  12:17:47  Show Profile  Visit nr's Homepage  Reply with Quote
Try it with
'qwe,wqer,qwe,ew12356yui231iy3,,,123,123123tuytyut,,,123'
it will return
12356,231,3,123,123123,123
instead of
123562313,123,123123,123

The fields you have only have trailng non-numeric characters rather than having them embedded and starting the string also.
It's why I was trying to clarify the requirement - it wasn't clear from the original post. I still doubt if this is what's really wanted.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

skysyb
Starting Member

United Kingdom
16 Posts

Posted - 04/21/2008 :  07:03:06  Show Profile  Reply with Quote
Thanks to all again. sorry, was out for couple of days and haven't got a chance to connect to internet. Apparently i can't create functions as we are on sybase 12.5.4 (soon we may go to 15, then I can write functions too). However, I did use the logic of nr's and it absolutely worked fine (cheers Nr)!!

Thanks to all of you again for helping me w/ this. Truly appreciated !.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.38 seconds. Powered By: Snitz Forums 2000