Solved

remove / from data in SQL Server

Posted on 2013-12-01
4
490 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
  • 3
4 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

685 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