I am having a varchar datatype column in a table . For Example in the column if the string is "ABC001" I want to retrieve "ABC" from the string and store in variable. The string may be differ in length it was not in fixed length. Please suggest me how
to resolve.
We can change the script given there and use it to clear numeric values from string as follows:
create function ClearNumericCharacters(@str nvarchar(max))
returns nvarchar(max)
as
begin
while patindex('%[0-9]%', @str) > 0
set @str = stuff(@str, patindex('%[0-9]%', @str), 1, '')
return @str
end
In referred tutorial, there are samples how you can use the sql function on a variable or with columns of a database table,
CREATE TABLE Contacts ( mixedCol NVARCHAR(100) )
INSERT INTO Contacts VALUES ( 'abc001'),( 'efg56789')
--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
,processCTE as (
select mixedCol, col as Number_removed_mixedCol
from Contacts Cross Apply (
select (select i + ''
from (select N, substring(mixedCol, N, 1) i from Nums
where N<=datalength(mixedCol)) t
where PATINDEX('%[^0-9]%',i)> 0
order by N
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
) p0 (col)
)
SELECT mixedCol, Number_removed_mixedCol FROM processCTE
drop table Contacts
Member
1 Points
109 Posts
How to fetch a string from a table column in sql
Aug 16, 2016 07:08 AM|ykkumar13|LINK
Hi,
I am having a varchar datatype column in a table . For Example in the column if the string is "ABC001" I want to retrieve "ABC" from the string and store in variable. The string may be differ in length it was not in fixed length. Please suggest me how to resolve.
All-Star
187714 Points
27197 Posts
Moderator
Re: How to fetch a string from a table column in sql
Aug 16, 2016 07:18 AM|Mikesdotnetting|LINK
You can use Regex for this.
ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
Member
1 Points
109 Posts
Re: How to fetch a string from a table column in sql
Aug 16, 2016 07:29 AM|ykkumar13|LINK
Actually I want to fetch the string in SQL procedure itself. Can you suggest me how to resolve.
Contributor
6071 Points
1439 Posts
Re: How to fetch a string from a table column in sql
Aug 16, 2016 10:47 AM|eralper|LINK
Hello ykkumar13,
Please read the SQL tutorial Remove Non-Numeric Character in SQL String Expression where a user function is created to remove alpha-numeric values in a string expression or character type field value
We can change the script given there and use it to clear numeric values from string as follows:
In referred tutorial, there are samples how you can use the sql function on a variable or with columns of a database table,
I hope it helps,
SQL Server 2017
All-Star
122732 Points
9858 Posts
Moderator
Re: How to fetch a string from a table column in sql
Aug 16, 2016 01:52 PM|limno|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
1 Points
109 Posts
Re: How to fetch a string from a table column in sql
Aug 18, 2016 05:07 AM|ykkumar13|LINK
Thank you very much it is working for me.