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

SyBase SQL Statement

Hello Experts,

I have a table with a column that contains sensitive data. I want to mask the data with asterix (*) but leave the last 6 characters unmasked.

Any help with the SQL query would be appreciated.

TIA.
0
triphen
Asked:
triphen
  • 6
  • 5
7 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would suggest that you use something like this:

select yourfield,

  repeat( '*', length(yourfield) - 6 ) + substring(yourfield, -6, -1)

 from yourtable
 

Open in new window


http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc38151.1260/html/iqref/Substr.htm
http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc38151.1260/html/iqref/Substr.htm
0
 
Joe WoodhousePrincipal ConsultantCommented:
Depending on which Sybase product you're using, the syntax may be a little different ("replicate" rather than "repeat"; "len" rather than "length").

In ASE you might want to use "stuff" instead:

   select stuff(your_column, 1, len(your_column)-6, "*")

You can also create a view with these expressions, so you can revoke most permissions on the underlying table and grant permissions on the view instead. Users and apps then treat the view as a table.
0
 
triphenAuthor Commented:
angelIII

Query:
select cnum,

repeat( '*', length(cnum) - 6 ) + substring(cnum, -6, -1)

from dba.hauth

Error:
[Sybase][ODBC Driver][SQL Anywhere]Function 'substr' has invalid parameter '3' ('-1')
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Joe WoodhousePrincipal ConsultantCommented:
Can you tell us exactly which Sybase database product you're using, and it's version string?
0
 
triphenAuthor Commented:
Sorry should have included this in the way beginning :)

SQL Anywhere Network Server v10.0.1.4181
0
 
Joe WoodhousePrincipal ConsultantCommented:
Aha! STUFF should still work per my ASE example, but you'll need LENGTH rather than LEN.

Although I am puzzled by your error since SUBSTRING can take negative lengths and starting positions in ASA.
0
 
triphenAuthor Commented:
Query:
select stuff(CNUM, 1, length(CNUM)-6, "*")

Error:
[Sybase][ODBC Driver][SQL Anywhere]Column 'CNUM' not found.


Where am I specifying my table name?

What does "stuff" mean?

I don't get that.
0
 
Joe WoodhousePrincipal ConsultantCommented:
You still need the FROM and any WHERE clauses. ASA quite reasonably says "ok, you want this CNUM thing, but what's a CNUM?".

     select stuff(CNUM, 1, length(CNUM)-6, "*")
     from <you_table>
     where [...]

"Stuff" is a function to take a string and stuff some of full of some other string. Our example says "take CNUM, starting at the first character and until it's 7th-last character, replace it with an asterisk, but in all other positions just show the original string".

i.e. it's exactly what you want to do, but it doesn't take negative relative offsets, so we have to figure out exactly what the "7th-last" is, and only STUFF it full of asterisks up to that point.
0
 
triphenAuthor Commented:
Query:
select stuff(CNUM, 1, length(CNUM)-6, "*") from dba.hauth

Error:
[Sybase][ODBC Driver][SQL Anywhere]Column '*' not found.
0
 
Joe WoodhousePrincipal ConsultantCommented:
Try single quotes?
0
 
triphenAuthor Commented:
It returns (Memo)


Also, I dont want to view or select this data, I want to update the current info and mask it with **********123456.
0
 
Joe WoodhousePrincipal ConsultantCommented:
Use the same expression in an UPDATE statement. You'll be updating each column you want to do this to, to an expression based on its old value.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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