Need HELP Converting Strings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need HELP Converting Strings

Author

Topic

gator8869
Starting Member

37 Posts

Posted - 2007-08-23 : 17:26:43

I need to convert string data so that I can use it in a view.

example
xxx-xx-xx
xxx-xx-xx
xxxxxxx
xxxxxxx
xxxxxxx
xxx
xx
I need to be able to add the dashes in for the data that has 7 characters and disregard the data that is less then 7 characters

Jeff

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 17:51:10

Something like this to start with:


SELECT substring(column, 1,3) + '-' + Substring(column, 4,2) +  '-' + Substring(column, 6,2)
FROM YourTable
WHERE Len(Column) = 6

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

gator8869
Starting Member

37 Posts

Posted - 2007-08-23 : 18:07:46

THANK YOU

gator8869
Starting Member

37 Posts

Posted - 2007-08-23 : 19:28:30

MR1NEW MR2NEW (No Column Name)
159-08-03 NULL 000-00-00
137-03-04 NULL 000-00-00
095-11-02 NULL 000-00-00
079-06-91 NULL 000-00-00
050-08-97 NULL 000-00-00
037-12-93 NULL 000-00-00
061-01-04 NULL 000-00-00
186-05-04 NULL 000-00-00
062-03-03 NULL 000-00-00
120-06-04 NULL 000-00-00
137-07-03 NULL 000-00-00
113-11-70 NULL 000-00-00
072-07-04 NULL 000-00-00
229-03-98 NULL 000-00-00
034-09-78 226-11-76 2261176
229-03-98 NULL 000-00-00
226-11-76 NULL 000-00-00
138-06-04 NULL 000-00-00

I would like to replace the NULL in MR2 With 000-00-00, and leave to not nulls

Jeff

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 19:31:42

UPDATE Table
SET MR2NEW = '000-00-00'
WHERE MR2NEW IS NULL

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

gator8869
Starting Member

37 Posts

Posted - 2007-08-23 : 19:41:53

This is a view that Im trying to update not a table...sorry

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 19:44:26

You can either use ISNULL or COALESCE.

SELECT substring(column, 1,3) + '-' + Substring(column, 4,2) + '-' + Substring(column, 6,2),
ISNULL(MR2NEW, '000-00-00') as MR2NEW
FROM YourTable
WHERE Len(Column) = 6

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

gator8869
Starting Member

37 Posts

Posted - 2007-08-23 : 20:20:53

CREATE VIEW Adverse_Incident_report_MR_fix
AS
SELECT *, Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR1,1,3) + '-' + Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR1,4,2) + '-' + Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR1,6,2) AS MR1NEW,
Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR2,1,3) + '-' + Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR2,4,2) + '-' + Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR2,6,2) AS MR2NEW,
Isnull(MR1, '000-00-00') as MR1TEMP,
Isnull(MR2, '000-00-00') as MR2TEMP
FROM dbo.FD__ADVERSE_INCIDENT_REPORT
WHERE (Len(dbo.FD__ADVERSE_INCIDENT_REPORT.MR1) = 7) or (Len(dbo.FD__ADVERSE_INCIDENT_REPORT.MR2) = 7)

I would like to replace the NULL values in MR1NEW and MR2NEW with 000-00-00 from MR1TEMP and MR2TEMP

Jeff

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 21:00:17

CREATE VIEW Adverse_Incident_report_MR_fix
AS
SELECT *,
MR1NEW = CASE WHEN dbo.FD__ADVERSE_INCIDENT_REPORT.MR1 IS NULL THEN '000-00-00' ELSE Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR1,1,3) + '-' + Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR1,4,2) + '-' + Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR1,6,2),
MR2NEW = CASE WHEN dbo.FD__ADVERSE_INCIDENT_REPORT.MR2 IS NULL THEN '000-00-00' ELSE Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR2,1,3) + '-' + Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR2,4,2) + '-' + Substring(dbo.FD__ADVERSE_INCIDENT_REPORT.MR2,6,2)
/*
Isnull(MR1, '000-00-00') as MR1TEMP,
Isnull(MR2, '000-00-00') as MR2TEMP
*/
FROM dbo.FD__ADVERSE_INCIDENT_REPORT
WHERE (Len(dbo.FD__ADVERSE_INCIDENT_REPORT.MR1) = 7) or (Len(dbo.FD__ADVERSE_INCIDENT_REPORT.MR2) = 7)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources