14,928,959 members
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

## Solution 1

Use Substring function to track the '_' character.

See its detailed use here:
http://msdn.microsoft.com/en-us/library/ms187748.aspx[^]

## Solution 2

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.

## Solution 3

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)

Top Experts
Last 24hrsThis month
 OriginalGriff 410 Richard MacCutchan 150 Dave Kreskowiak 135 CPallini 65 KarstenK 50
 OriginalGriff 4,102 Richard MacCutchan 1,845 Richard Deeming 1,435 Patrice T 1,055 CPallini 930

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