replace & trim - 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

General SQL Server Forums

New to SQL Server Programming

replace & trim

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-10-13 : 17:11:28

hi.I have a field which the contents look like this:

SG240_061608_supsm.jpg
SSIL_1207_supsm.jpg
supp_facts_11700_071306.bmp
supp_facts_AA_072707.gif
...
how can I get rid of the text and "-" & ".gif" & ".bmp" & ".jpg"?
all I need are the numbers of this field.
thanks

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 17:30:57

so how do you want 'supp_facts_11700_071306.bmp' look like? '11700071306'??

also, 'SG240_061608_supsm.jpg' as '240061608'??

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-10-13 : 17:43:21

yes exactly.and what if I only want the first part of each one befor the numbers:
supp_facts_11700_071306.bmp ----> supp_facts
SG240_061608_supsm.jpg -------> SG240
thank u so much

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 17:57:01

[code]
Create function fn_numbersonly(@str varchar(8000))
RETURNS varchar(8000) as
begin

WHILE PATINDEX('%[^0-9]%', @str )> 0
set @str = REPLACE(@str, SUBSTRING(@str,
PATINDEX('%[^0-9]%', @str),1),'')

return @str

END

select dbo.fn_numbersonly('SG240_061608_supsm.jpg')

newstr
-------------------------
240061608

(1 row(s) affected)
[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 18:01:16

for the 1st part only:


select substring(<YOURCOLUMN>,0,
charindex('_',replace(<YOURCOLUMN>,'supp_facts','supp-facts'))
) as newstring
from <YOURTABLE>

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-10-13 : 18:09:33

Thank you for help & quick reply :)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-13 : 18:27:26

For your second part,

CREATE FUNCTION ParseValuesU
(@String varchar(8000)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(100)
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX('_',@String) >0 THEN LEFT(@String,CHARINDEX('_',@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX('_',@String) >0 THEN SUBSTRING(@String,CHARINDEX('_',@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

select Val from [DBNAME].[dbo].[ParseValuesU]('SG240_061608_supsm.jpg')
where id =
(SELECT top 1 ID FROM [DBNAME].[dbo].[ParseValuesU]('SG240_061608_supsm.jpg' ) where isnumeric(val)=1 order by 1 asc)-1
----
SG240

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 18:54:29

sakets, the function is nice to parse strings, but it might be inefficient if you just want the 1st part of the the string, and he's updating 1million rows.
also, there's a special case where 'supp_facts' is the header not 'supp'. I don't know other special cases...


select substring('SG240_061608_supsm.jpg',0,
charindex('_',replace('SG240_061608_supsm.jpg','supp_facts','supp-facts'))
) as newstring

select substring('supp_facts_11700_071306.bmp',0,
charindex('_',replace('supp_facts_11700_071306.bmp','supp_facts','supp-facts'))
) as newstring

newstring              
---------------------- 
SG240

(1 row(s) affected)

newstring                   
--------------------------- 
supp_facts

(1 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 00:36:30

SELECT SUBSTRING(Col1, 1, PATINDEX('%[_][0-9]%', Col1) - 1)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources