December 24, 2015 at 1:37 pm
Hi all,
i have the next problem:
i need to extract numbers from a string.
Without using CTE, func, proc. Just query.
Restrictions - varchar (50)
Let`s take this example
select 'aGk5kyO8pfas2csd444567' as word union all select 'Fl7Ac3lmsds226jj'
December 24, 2015 at 2:10 pm
Basurmanin (12/24/2015)
Hi all,i have the next problem:
i need to extract numbers from a string.
Without using CTE, func, proc. Just query.
Restrictions - varchar (50)
Let`s take this example
select 'aGk5kyO8pfas2csd444567' as word union all select 'Fl7Ac3lmsds226jj'
Care to show what the desired output?
And why would you not be able to use CTEs or function?
You can't manipulate any part of any value without using at least one of the MS supplied functions...
December 24, 2015 at 2:46 pm
Check out this thread
😎
December 24, 2015 at 2:56 pm
Basurmanin (12/24/2015)
Hi all,i have the next problem:
i need to extract numbers from a string.
Without using CTE, func, proc. Just query.
Restrictions - varchar (50)
Let`s take this example
select 'aGk5kyO8pfas2csd444567' as word union all select 'Fl7Ac3lmsds226jj'
Why the restrictions? Are you actually using SQL Server or something else?
Also, do you want 582444567 extracted as a single value from your first example or do you want...
5
8
2
444567
... as separate values?
As a big of a side bar, not allowing iTVFs (inline Table Valued Functions) is a huge mistake both performance wise and code maintainability wise. Same goes with not being able to use CTEs or stored procedures.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 24, 2015 at 8:31 pm
I don't understand the restrictions either. ITVFs and CTEs are a part of SQL Server and can accomplish some great things, like your exact requirement. Above all else, don't use a WHILE loop if you want it to perform well.
December 24, 2015 at 10:14 pm
Hello, 582444567
December 25, 2015 at 7:35 am
Basurmanin (12/24/2015)
Hello, 582444567
Goodbye...
December 25, 2015 at 8:43 am
Basurmanin (12/24/2015)
Hello, 582444567
Hello. Were you able to figure out the reason behind your restrictions?
December 25, 2015 at 11:09 am
take it easy
December 25, 2015 at 11:13 am
Ed Wagner,
i need so, i ask a question and it contains without CTE, proc, func and varchar (50)
Do you have idea how to realise it?
December 25, 2015 at 11:14 am
Old Hand,
take it easy
December 25, 2015 at 11:45 am
Basurmanin (12/25/2015)
take it easy
First, I agree... as frustrating as questions with seemingly unwarranted restrictions go, people need to "Take it Easy".
You can help diffuse such responses. People are always curious as to why such restrictions have been placed other than just "I need so" to better understand 1) how to help you (see below) and 2) become more aware of when such restrictions may enter their own world.
Basurmanin (12/25/2015)
Ed Wagner,i need so, i ask a question and it contains without CTE, proc, func and varchar (50)
Do you have idea how to realise it?
Yes. There are actually many ways to do this but... Part of the reason to know WHY you have such restrictions is because you may not be aware of more restrictions and people are just trying to help but don't want to waste your or their time shooting in the dark. For example, this can easily be done without a CTE, proc, or user defined function. But, there may be more restrictions involved. One very fast method requires the use of a sub-query in a FROM clause and the use of FOR XML PATH. Are those allowed? Another very fast method involves having access to a table in the Master database if neither of those other things are allowed.
So, let's everyone start over fresh. Let's start with you... WHY have such restrictions been placed on you? For example, are you using the SQL Server CE (Compact Edition), which has a huge number of such restrictions? If not, WHAT is the driving reason behind such restrictions? We need to know so that we can anticipate other possible restrictions and provide you with the best possible answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2015 at 1:13 pm
Like Jeff pointed out, understanding why the restrictions you listed exist is paramount to determining the solution. That's why we ask questions - to understand what's required and the "why" that lies beneath it.
The following is an ITVF that uses a CTE of numbers to extract the numeric characters and then reassemble them.
if object_id('dbo.CleanNumbers', 'if') is not null drop function dbo.CleanNumbers;
go
CREATE FUNCTION dbo.CleanNumbers(@OriginalText Varchar(8000))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
Tally(N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteCleaned(CleanText) AS (
SELECT SUBSTRING(@OriginalText, t.N, 1)
FROM Tally t
WHERE ASCII(SUBSTRING(@OriginalText, t.N, 1)) BETWEEN 48 AND 57
FOR XML PATH('')
)
SELECT CleanText
FROM cteCleaned;
go
Because it doesn't meet your restrictions, it will have to be rewritten another way. Without understanding the reasoning behind your restrictions, I don't have anywhere to go with it.
December 25, 2015 at 1:18 pm
The link Eirikur posted also contains an excellent discussion on the topic.
December 28, 2015 at 1:08 pm
I'd at least compare the performance of the straightforward brute-force method:
SELECT
word AS original_string,
CASE WHEN SUBSTRING(word, 01, 1) LIKE '[0-9]' THEN SUBSTRING(word, 01, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 02, 1) LIKE '[0-9]' THEN SUBSTRING(word, 02, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 03, 1) LIKE '[0-9]' THEN SUBSTRING(word, 03, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 04, 1) LIKE '[0-9]' THEN SUBSTRING(word, 04, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 05, 1) LIKE '[0-9]' THEN SUBSTRING(word, 05, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 06, 1) LIKE '[0-9]' THEN SUBSTRING(word, 06, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 07, 1) LIKE '[0-9]' THEN SUBSTRING(word, 07, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 08, 1) LIKE '[0-9]' THEN SUBSTRING(word, 08, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 09, 1) LIKE '[0-9]' THEN SUBSTRING(word, 09, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 10, 1) LIKE '[0-9]' THEN SUBSTRING(word, 10, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 11, 1) LIKE '[0-9]' THEN SUBSTRING(word, 11, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 12, 1) LIKE '[0-9]' THEN SUBSTRING(word, 12, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 13, 1) LIKE '[0-9]' THEN SUBSTRING(word, 13, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 14, 1) LIKE '[0-9]' THEN SUBSTRING(word, 14, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 15, 1) LIKE '[0-9]' THEN SUBSTRING(word, 15, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 16, 1) LIKE '[0-9]' THEN SUBSTRING(word, 16, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 17, 1) LIKE '[0-9]' THEN SUBSTRING(word, 17, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 18, 1) LIKE '[0-9]' THEN SUBSTRING(word, 18, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 19, 1) LIKE '[0-9]' THEN SUBSTRING(word, 19, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 20, 1) LIKE '[0-9]' THEN SUBSTRING(word, 20, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 21, 1) LIKE '[0-9]' THEN SUBSTRING(word, 21, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 22, 1) LIKE '[0-9]' THEN SUBSTRING(word, 22, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 23, 1) LIKE '[0-9]' THEN SUBSTRING(word, 23, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 24, 1) LIKE '[0-9]' THEN SUBSTRING(word, 24, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 25, 1) LIKE '[0-9]' THEN SUBSTRING(word, 25, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 26, 1) LIKE '[0-9]' THEN SUBSTRING(word, 26, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 27, 1) LIKE '[0-9]' THEN SUBSTRING(word, 27, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 28, 1) LIKE '[0-9]' THEN SUBSTRING(word, 28, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 29, 1) LIKE '[0-9]' THEN SUBSTRING(word, 29, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 30, 1) LIKE '[0-9]' THEN SUBSTRING(word, 30, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 31, 1) LIKE '[0-9]' THEN SUBSTRING(word, 31, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 32, 1) LIKE '[0-9]' THEN SUBSTRING(word, 32, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 33, 1) LIKE '[0-9]' THEN SUBSTRING(word, 33, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 34, 1) LIKE '[0-9]' THEN SUBSTRING(word, 34, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 35, 1) LIKE '[0-9]' THEN SUBSTRING(word, 35, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 36, 1) LIKE '[0-9]' THEN SUBSTRING(word, 36, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 37, 1) LIKE '[0-9]' THEN SUBSTRING(word, 37, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 38, 1) LIKE '[0-9]' THEN SUBSTRING(word, 38, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 39, 1) LIKE '[0-9]' THEN SUBSTRING(word, 39, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 40, 1) LIKE '[0-9]' THEN SUBSTRING(word, 40, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 41, 1) LIKE '[0-9]' THEN SUBSTRING(word, 41, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 42, 1) LIKE '[0-9]' THEN SUBSTRING(word, 42, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 43, 1) LIKE '[0-9]' THEN SUBSTRING(word, 43, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 44, 1) LIKE '[0-9]' THEN SUBSTRING(word, 44, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 45, 1) LIKE '[0-9]' THEN SUBSTRING(word, 45, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 46, 1) LIKE '[0-9]' THEN SUBSTRING(word, 46, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 47, 1) LIKE '[0-9]' THEN SUBSTRING(word, 47, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 48, 1) LIKE '[0-9]' THEN SUBSTRING(word, 48, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 49, 1) LIKE '[0-9]' THEN SUBSTRING(word, 49, 1) ELSE '' END +
CASE WHEN SUBSTRING(word, 50, 1) LIKE '[0-9]' THEN SUBSTRING(word, 50, 1) ELSE '' END AS result_string
FROM dbo.table_name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply