?
Solved

remove / from data in SQL Server

Posted on 2013-12-01
4
Medium Priority
?
498 Views
Last Modified: 2013-12-01
Hello Experts, I have a little problem (some may say a big problem .... but lets not go there); now that we have moved past the innuendo :D I have a field on a MS SQL 2012 table that contains data in to following format:

xx//xxxxxxx or xx/x/xxxxxxxx or A/a/xxxxxxx

the data type is NVARCHAR the values represent cadastral parcels.

my question is how do I remove ONLY / from the field in a view (it is causing some problems when rendering in another application).

Thankyou in advance
0
Comment
Question by:XGIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39688798
In a view you need to use a string function to change the field's value. If you want to remove all / characters, then you need to use a REPLACE function. Assuming, you table is called Table1, and the column with the values is called Column1, then you can create a view, like this:
CREATE VIEW View1 AS
SELECT REPLACE(Column1, N'/', N'') AS Column1,
Column2,
Column3 -etc. List all other columns required for your application
FROM Table1
-- optionally you can JOIN other tables, add some filters using a WHERE clause, etc.

Open in new window

0
 
LVL 7

Author Comment

by:XGIS
ID: 39688800
Hello all, just after posting I tried some different things and found the replace function..... I know ....duh right but any way the more you know.

REPLACE(column, '/', '')

Thanks for your time.
0
 
LVL 7

Author Comment

by:XGIS
ID: 39688801
I've requested that this question be closed as follows:

Accepted answer: 0 points for XGIS's comment #a39688800

for the following reason:

Solved the solution myself
0
 
LVL 7

Author Closing Comment

by:XGIS
ID: 39688802
bit late but a solution none the less...
thanks.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question