Could you please elaborate in detail what does it means and how do i code in tsql

Where illegal characters are found then the records will be flagged for exclusion. For the purposes of scale , this should refer to a look up table so that other characters can be added as required

I have to create uSP which includes input parameters - praram1,Output parameters - flag_name, exclusion

and I have a lookup table to match for illegal characters ex: @ , . ] [ ) ( " % $ etc...


May 28th, 2015 4:29am

How Can I match invalid characters in string, I have lookup table contains one column with values ( ?,@,,$ etc., )

example: I have one input_param varchar(50)='s@m sand33p' , output_flaf char(1)

I need write one Usp if input string contains invalid characters output should me flag, we have to use loopup table for matching invalid characters.

May 28th, 2015 5:10am

declare @string varchar(200)

set @string = 'this $%^^&is%^& s2342om23&&({}e c76l232e+_+a#n/ c][#o''d#e'

 

select cast(cast((select substring(@string,n,1)

from numbers

where n <= len(@string)

and substring(@string,n,1) like '[a-z ]' for xml path('')) as xml)as varchar(max))
Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 5:20am

I didn't get what is 'n' and numbers
May 28th, 2015 5:28am

declare @string varchar(200)

set @string = 'this $%^^&is%^& s2342om23&&({}e c76l232e+_+a#n/ c][#o''d#e'

 

select cast(cast((select substring(@string,n,1)

from numbers

where n <= len(@string)

and substring(@string,n,1) like '[a-z ]' for xml path('')) as xml)as var
Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 5:31am

Hi Learner,

I'd use the CLR FUNCTION (Common Language Runtime) to manipulate the string with Regular Expression (REGEX). It's more recommended in my opinion. It's faster and efficient.  Regex.IsMatch (string,@"^[^\d]{2,}$"))

May 28th, 2015 10:30am

Try this:

DECLARE @badStrings TABLE (badString NVARCHAR(200))
INSERT INTO @badStrings (badString) VALUES
('abcdefghijklmnopqrstuvwxyz'),
('?@$'),
('0123456789')

DECLARE @badChars TABLE (badChar NCHAR(1))
INSERT INTO @badChars (badChar) VALUES
('?'),('@'),(''),('$ ')

SELECT badString, SUM(CASE WHEN CHARINDEX(badChar,badString) > 0 THEN 1 ELSE 0 END) AS badCharCount
  FROM @badStrings
    CROSS APPLY @badChars
 GROUP BY badString

Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 10:47am

Hello,

To achieve your requirement, you can reference the sample below.

DECLARE @input_param NVARCHAR(50)='s@m sand33p' 
DECLARE @output_flag CHAR(1) = '0'


DECLARE @InvalidCHARs TABLE (InvalidCHAR NCHAR(1))
INSERT INTO @InvalidCHARs VALUES(N'?'),(N'@'),(N''),(N'$ ')
 
SELECT @output_flag = 1 WHERE EXISTS(SELECT 1 FROM @InvalidCHARs WHERE CHARINDEX(InvalidCHAR,@input_param)<>0)

PRINT @output_flag

If you have any question, feel free to let me know.
May 28th, 2015 11:24pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics