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

hidden word in column

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


how to select fields where 'word' is hidden
0
rgb192
Asked:
rgb192
1 Solution
 
Anuradha GoliCommented:
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';

Open in new window

0
 
ralmadaCommented:
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                    | 
+----------------------------+ 

Open in new window


http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#815
0
 
tliottaCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
rgb192Author 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
 
tliottaCommented:
So you just want WHERE varcharcolumn1 LIKE '%w%o%r%d%' ?

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

Tom
0
 
rgb192Author Commented:
easiest
thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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