string formatting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

string formatting

Author

Topic

skysyb
Starting Member

16 Posts

Posted - 2008-04-14 : 06:58:27

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

12543 Posts

Posted - 2008-04-14 : 07:01:35

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 07:06:03

How about this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-14 : 08:35:49

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

skysyb
Starting Member

16 Posts

Posted - 2008-04-14 : 14:02:47

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 !!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 14:06:37

Have a look at CHARINDEX function in BOL

skysyb
Starting Member

16 Posts

Posted - 2008-04-14 : 14:19:57

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.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 14:48:58

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 16:10:12

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"

skysyb
Starting Member

16 Posts

Posted - 2008-04-15 : 06:15:38

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..

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-15 : 12:41:03

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.

skysyb
Starting Member

16 Posts

Posted - 2008-04-15 : 15:11:55

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-16 : 03:06:06

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-16 : 03:15:00

I thought the function posted 04/14/2008 : 16:10:12 did all that?

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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-16 : 05:14:36

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.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-16 : 05:28:41

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-16 : 06:19:52

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"

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-16 : 12:17:47

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.

skysyb
Starting Member

16 Posts

Posted - 2008-04-21 : 07:03:06

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 !.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources