Tips and Tricks in a world of Mix

Archive for the ‘DataBase’ Category

Remove Special Charachters \n\r CR LF from data PL / SQL Oracle and .Net

In ORACLE

SELECT REPLACE(REPLACE(YourColumn,CHAR(13),NULL),CHAR(10),NULL)
FROM YourTable


FOR ONE CHAR :
SELECT replace(YourColumn,CHAR(13),'')
FROM YourTable

OR
update YourTable
set YourColumn =replace(YourColumn,CHAR(13),'')

IN .NET 
MyString.Replace("\r\n","")   

or..

MyString.Replace("\r\n",@"\n")

Potential Blacklist
Special control chars 0-31 by Unicode must be handled
White list 
[^a-zA-Zא-ת0-9._] -and punctuation chars by your choice
- this is a start of a regex pattern - [^] -everything that not in the pattern is not allowed.
also can do 
[^\w\s\p{IsHebrew}\p{P}] - not taking accesnt chars like ~^ , not finished , but a start

better handled by StringBuilder with loop through chars than Regex 
(more than 10 times better).just Whitelist of allowed chars and if in range 
like this 

if(c>=a && c<=z)...
  sb.append(c)
else
  sb.append(' '); //replacement char
 
Advertisements

Sql Server 2008 – forgot the sa password ( how to retrieve sa password )

go to run line

>cmd

Net Stop MSSQLSERVER

Net Start MSSQLSERVER /c /m /T3604

– Now your SqlServer running in a single mode.

Enter the Sql Server Managment Studio with server name (local) or just write  “.”  with Window Authentication.

Security –> Logins –> your Window Authentication user –> right click –> properties –> server roles –> check sysadmin

Now you can change the sa password.

Security –> Logins –> sa –> right click –> properties –> general –> enter password –> enter confirm password (pay attention for the password policy – it should have letters numbers and special chars in one ). –> ok

Don’t forget to return to a regular mode , hence :

> cmd

Net Stop MSSQLSERVER

Net Start MSSQLSERVER

You got your password retrieved and the SqlServer2008 running .

That’s it !

Same with (sqlexpress)

For people having trouble with SQL Server 2008:

1. You need to first login to windows with a local administrator account.

2. You need to right-click “Command Prompt” and select “Run as Administrator”

3. Type: net stop “sql server (sqlexpress)”

4. Type: net start “sql server (sqlexpress)” /m

5. Right-click on SSMA Express and select “Run as Administrator”

6. Click “New Query” button and login to SQLExpress instance with “Windows Authentication”.

7. Type: SELECT user_name()
and hit F5 to execute. Make sure it says “dbo”

8. Delete previous statement. Then type: sp_addsrvrolemember ‘YOURDOMAIN\username’,’sysadmin’
YOURDOMAIN needs to be replaced by your domain, or if the account you’re trying to add to sysadmin isn’t a domain account then just type only the username.

9. Stop the SQL Server through Command Prompt again, and start it again but without the: /m

10. Logout of the local admin account, login with the network account that you just added as a sysadmin and “Run as Administrator” on SQL Server Management Studio Express.

Get all your tables that not in your string array

select table_name
from user_tables
where table_name not like ‘%<some condition if needed>%’
and table_name not in (  ‘value_table1’, ‘ value_table2’… )

 

Tag Cloud

%d bloggers like this: