Click here to Skip to main content
14,928,959 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,
I have record "20112312438_AjaxURLS.txt_123546675" where I need to display in grid view only "AjaxURLS.txt" .What will be the query for that to replace all number and special character ? please help
Posted
Updated 17-Feb-11 2:17am
v2

Use Substring function to track the '_' character.

See its detailed use here:
http://msdn.microsoft.com/en-us/library/ms187748.aspx[^]
   
select charindex('_',[Fieldname]) will give an integer for the underscore

select substring([Fieldname],start,length) will reduce the string

use a combination to reduce your field as required. You will need nested queries to do this.
   
Slightly modified from the answer given here[^]
Check if it may help you
SQL
DECLARE @InputString NVARCHAR(MAX)
DECLARE @Delimiter NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextString NVARCHAR(40)
SET @InputString = '20112312438_AjaxURLS.txt_123546675'
SET @Delimiter = 1
SET @Pos = 1
SET @InputString = @InputString + @Delimiter
CREATE TABLE #Results(Tokens NVARCHAR(MAX))
WHILE (@pos<>0)
BEGIN
    SET @NextString = replace(@inputstring,@delimiter,'')
    set @delimiter=@delimiter+1
    INSERT INTO #Results VALUES (@NextString)
    update #results set tokens=@nextstring
    SET @InputString = replace(@NextString,'_','')
    SET @pos = charindex(@Delimiter,@InputString)
END
SELECT distinct replace(tokens,'0','') FROM #Results
DROP TABLE #Results
   
Comments
IndrajitDasgupat 18-Feb-11 7:50am
   
Thanks it is working fine
m@dhu 18-Feb-11 7:52am
   
Nice it helped you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900