• Status: Solved
• Priority: Medium
• Security: Public
• Views: 777

# hidden word in column

varcharcolumn1=1w23o45rd
1w23o45678r9d
'word' is hidden in column

how to select fields where 'word' is hidden
0
rgb192
1 Solution

Commented:
``````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

Commented:
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

Commented:
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 Commented:
>>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

Commented:
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 Commented:
easiest
thanks
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.