Solved

hidden word in column

Posted on 2012-08-31
754 Views
varcharcolumn1=1w23o45rd
1w23o45678r9d
'word' is hidden in column

how to select fields where 'word' is hidden
0
Question by:rgb192

LVL 12

Expert Comment

``````CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^A-Z]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^A-Z]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

SELECT dbo.udf_GetNumeric(' 1w23o45678r9d') AS 'DATA';
SELECT dbo.udf_GetNumeric(' 1w23o45rd') AS 'DATA1';
``````
0

LVL 41

Expert Comment

In mysql create this function

``````DROP FUNCTION IF EXISTS alphas;
DELIMITER |
CREATE FUNCTION alphas( str CHAR(255) ) RETURNS CHAR(255)
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(255) DEFAULT '';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c REGEXP '[[:alpha:]]' THEN
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;
SELECT alphas('123ab45cde6789fg');
+----------------------------+
| alphas('123ab45cde6789fg') |
+----------------------------+
| abcdefg                    |
+----------------------------+
``````

http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#815
0

LVL 26

Expert Comment

Do you always want the same word? Unlikely, but possible. Or do you want to select rows where any given supplied word is found?

Tom
0

Author Comment

>>Do you always want the same word? Unlikely, but possible. Or do you want to select rows where any given supplied word is found?

always want the same word
'word'
0

LVL 26

Accepted Solution

So you just want WHERE varcharcolumn1 LIKE '%w%o%r%d%' ?

Somehow that seems simplistic, but it should be all you need.

Tom
0

Author Closing Comment

easiest
thanks
0

Featured Post

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…