I have a large database in which I want to do a part string search. The user will enter characters: JoeBloggs.
For arguments sake if I had a name Joe 23 Blo Ggs 4
in the database. I want to remove everything in the name other than A-Z.
I have the REPLACE(Name, ' ','')
function to remove spaces and the UPPER()
function to capitalize the name.
Is there a more efficient fast way maybe by terms of regex to replace anything other than A-Z. I cannot change the values in the database.
This question is related to
sql
sql-server
sql-server-2008
Try below for your query. where val is your string or column name.
CASE WHEN PATINDEX('%[a-z]%', REVERSE(val)) > 1
THEN LEFT(val, LEN(val) - PATINDEX('%[a-z]%', REVERSE(val)) + 1)
ELSE '' END
1st option -
You can nest REPLACE()
functions up to 32 levels deep. It runs fast.
REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE (@str, '0', ''),
'1', ''),
'2', ''),
'3', ''),
'4', ''),
'5', ''),
'6', ''),
'7', ''),
'8', ''),
'9', '')
2nd option -- do the reverse of -
Removing nonnumerical data out of a number + SQL
3rd option - if you want to use regex
Quoting part of @Jatin answer with some modifications,
use this in your where
statement:
SELECT * FROM .... etc.
Where
REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE (Name, '0', ''),
'1', ''),
'2', ''),
'3', ''),
'4', ''),
'5', ''),
'6', ''),
'7', ''),
'8', ''),
'9', '') = P_SEARCH_KEY
Remove everything after first digit (was adequate for my use case): LEFT(field,PATINDEX('%[0-9]%',field+'0')-1)
Remove trailing digits: LEFT(field,len(field)+1-PATINDEX('%[^0-9]%',reverse('0'+field))
Not tested, but you can do something like this:
Create Function dbo.AlphasOnly(@s as varchar(max)) Returns varchar(max) As
Begin
Declare @Pos int = 1
Declare @Ret varchar(max) = null
If @s Is Not Null
Begin
Set @Ret = ''
While @Pos <= Len(@s)
Begin
If SubString(@s, @Pos, 1) Like '[A-Za-z]'
Begin
Set @Ret = @Ret + SubString(@s, @Pos, 1)
End
Set @Pos = @Pos + 1
End
End
Return @Ret
End
The key is to use this as a computed column and index it. It doesn't really matter how fast you make this function if the database has to execute it against every row in your large table every time you run the query.
One more approach using Recursive CTE..
declare @string varchar(100)
set @string ='te165st1230004616161616'
;With cte
as
(
select @string as string,0 as n
union all
select cast(replace(string,n,'') as varchar(100)),n+1
from cte
where n<9
)
select top 1 string from cte
order by n desc
**Output:**
test
This one works for me
CREATE Function [dbo].[RemoveNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
Declare @NumRange as varchar(50) = '%[0-9]%'
While PatIndex(@NumRange, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')
Return @Temp
End
and you can use it like so
SELECT dbo.[RemoveNumericCharacters](Name) FROM TARGET_TABLE
Source: Stackoverflow.com